Every row in a table started life as an INSERT statement. This lesson covers adding a single row, adding several at once, and the one genuinely database-specific wrinkle in this topic: getting back the ID that the database just generated for a new row.
Learning Objectives
- Write an INSERT statement for a single new row.
- Insert multiple rows in one statement.
- Retrieve the auto-generated ID of a row you just inserted.
INSERT INTO
Table and column names
VALUES
The actual data
Row Added
Database assigns storage
ID Generated
If the table has an auto-increment column
Inserting a Single Row
List the columns you're providing values for, then the matching values in the same order. Any column left out gets its default value, or NULL if it has no default and allows NULL.
INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Katherine', 'Johnson', 'Research', 92000);Inserting Multiple Rows
One VALUES clause per row, comma-separated. This is a single statement and a single round trip to the database, meaningfully faster than running separate INSERT statements in a loop for each row.
INSERT INTO employees (first_name, last_name, department, salary)
VALUES
('Dorothy', 'Vaughan', 'Engineering', 91000),
('Mary', 'Jackson', 'Engineering', 89000);Getting the New Row's ID Back
After inserting a row with an auto-generated primary key, you usually need that ID right away, to insert a related row, or to show the user what they just created. How you get it back is one of the few places SQL genuinely splits by database.
INSERT INTO employees (first_name, last_name) VALUES ('Ada', 'Lovelace');
SELECT LAST_INSERT_ID();INSERT INTO employees (first_name, last_name)
VALUES ('Ada', 'Lovelace')
RETURNING id;INSERT INTO employees (first_name, last_name) VALUES ('Ada', 'Lovelace');
SELECT SCOPE_IDENTITY();INSERT INTO employees (first_name, last_name) VALUES ('Ada', 'Lovelace');
SELECT last_insert_rowid();PostgreSQL's RETURNING is the cleanest of the four, it gets the ID back in the same statement, no second query needed. The others require a separate follow-up call that reads the last ID generated on that same database connection.
Frequently Asked Questions
Do I always need to list column names in INSERT?
No, INSERT INTO employees VALUES (...) works without naming columns, but it requires a value for every column in the table's exact order, and breaks silently if someone adds a column later. Naming columns explicitly is safer and is what this course recommends.
What happens if I insert a row that violates a constraint, like a duplicate primary key?
The database rejects the entire INSERT and raises an error, no row is added. This is true across MySQL, PostgreSQL, SQL Server, and SQLite, constraints exist specifically to make this kind of bad data impossible to insert.
Can I insert the result of a SELECT query instead of literal values?
Yes, INSERT INTO table_a (col1, col2) SELECT col1, col2 FROM table_b WHERE ... copies rows from one table into another, no VALUES clause needed. This is standard SQL and works the same across all four databases.
Common Beginner Mistakes
Running one INSERT per row in a loop instead of a single multi-row INSERT
Each individual INSERT is a separate round trip to the database. Inserting 1,000 rows one at a time can be dramatically slower than one multi-row INSERT, or one INSERT inside a single transaction, covered in a later lesson.
Forgetting that column order in VALUES must match the column list exactly
INSERT INTO employees (first_name, last_name) VALUES ('Lovelace', 'Ada') silently swaps the names, it doesn't error, it just inserts wrong data in the right-shaped columns.
Assuming LAST_INSERT_ID() (or its equivalents) is safe under concurrent inserts from multiple connections
It isn't a global "most recent ID in the table," it's scoped to your own database connection/session, so it correctly returns your own insert's ID even if other connections are inserting at the same time. The risk is assuming it means something it doesn't, like the highest ID in the table.
Best Practices
- Always name columns explicitly in INSERT, never rely on positional VALUES matching the table's column order.
- Batch multiple rows into one INSERT statement instead of looping one row at a time.
- Use RETURNING (PostgreSQL) or the equivalent last-ID function immediately after the INSERT, on the same connection, don't try to look it up with a separate SELECT MAX(id) query, which is unsafe under concurrent writes.
- Wrap multi-row inserts that must all succeed or all fail together in a transaction, covered in a later lesson.
Interview Questions
Why is SELECT MAX(id) unsafe for getting the ID of a row you just inserted?
Between your INSERT and your SELECT MAX(id), another connection could insert a row with a higher ID, and you'd get their ID instead of yours. LAST_INSERT_ID(), RETURNING, SCOPE_IDENTITY(), and last_insert_rowid() are all scoped to your own connection/session specifically to avoid this race condition.
Write an INSERT that copies all Engineering employees into an archive table.
INSERT INTO employees_archive (first_name, last_name, department, salary) SELECT first_name, last_name, department, salary FROM employees WHERE department = 'Engineering'; — this inserts the SELECT's result rows directly, no VALUES clause.
What's the performance difference between 100 separate single-row INSERTs and one 100-row INSERT?
Each separate INSERT pays the cost of a full round trip to the database (network latency, parsing, a transaction commit in some configurations) on top of the actual write. Batching into one statement pays that overhead once, which can be an order of magnitude faster for bulk loads.
Summary
This lesson covered inserting a single row, inserting multiple rows in one statement, and the database-specific ways to retrieve a newly generated ID, RETURNING in PostgreSQL, and separate last-ID functions in MySQL, SQL Server, and SQLite.
What's Next?
The next lesson covers UPDATE, changing values in rows that already exist.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.