Every row in a table started life as an INSERT INTO statement. This lesson covers adding a single row and adding several at once.
Learning Objectives
- Write an INSERT INTO statement for a single new row.
- Insert multiple rows in one statement.
- Understand why naming columns explicitly is safer than relying on their order in the table.
INSERT INTO
Table and column names
VALUES
The actual data
Row Added
Database assigns storage
Done
New row exists in the table
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.
- + One round trip to the database instead of many, meaningfully faster for bulk loads
- + Either all rows insert or, if one violates a constraint, none do, atomic by default
- − Harder to tell exactly which row failed if the batch is rejected, compared to one-at-a-time inserts with per-row error handling
- − A very large single INSERT can hit a database's maximum statement size or parameter count
INSERT INTO employees (first_name, last_name, department, salary)
VALUES
('Dorothy', 'Vaughan', 'Engineering', 91000),
('Mary', 'Jackson', 'Engineering', 89000);Try It Yourself
Write an INSERT that adds one new employee, Hedy Lamarr, to the Engineering department with a salary of 93000.
INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Hedy', 'Lamarr', 'Engineering', 93000);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.
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. Constraints exist specifically to make this kind of bad data impossible to insert.
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 many rows one at a time can be dramatically slower than one multi-row INSERT.
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.
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.
Interview Questions
Write an INSERT adding two new employees in one statement.
INSERT INTO employees (first_name, last_name, department) VALUES ('Ada', 'Lovelace', 'Engineering'), ('Grace', 'Hopper', 'Engineering'); — one VALUES clause per row, comma-separated.
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 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 and inserting multiple rows in one statement, and why naming columns explicitly is safer than relying on the table's own column order.
What's Next?
The next lesson covers NULL values, what they actually mean, and how to check for them correctly.
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.