SQL / INSERTING, UPDATING, AND DELETING ROWS
Inserting many rows in one statement
Write one INSERT that loads many rows, explain why the whole statement is all-or-nothing, and pick batch sizes that engines and drivers accept.
What you will learn
- State the column list once, then one comma-separated tuple per row
- Expect all-or-nothing: one bad tuple discards every row in that statement
- Use DEFAULT inside a tuple when a single row should take the column default
- Chunk bulk loads; engines and drivers cap tuples and bind parameters
Understanding Inserting many rows in one statement
A multi-row insert keeps the usual INSERT INTO table (columns) header and then supplies several parenthesized tuples after VALUES, separated by commas. The column list is written once and binds positionally to every tuple, which is why each tuple must contain the same number of expressions in the same order. The mental model that explains the rest of this lesson is that VALUES is a table constructor: the engine assembles your tuples into one small table of rows and feeds it to the insert in a single pass, rather than running one insert per line you typed.
Because it is a single statement, it is also a single unit of failure. A NOT NULL violation, a type error, or a duplicate key in the last tuple aborts the statement, and the rows the engine had already written are discarded with it, even with no explicit BEGIN, because autocommit commits a statement only when the whole statement succeeded. A loop of single-row inserts behaves completely differently: each row commits on its own, so a failure halfway through leaves a partly loaded table and no obvious place to resume.
The other reason to group rows is cost. Every statement pays for parsing, planning, a network round trip, and under autocommit a durable commit write; a 500-tuple insert pays each of those once instead of 500 times, and index and log maintenance amortize better too. That does not mean one enormous statement is always best, since statement text size, per-statement row caps, bind parameter caps, and the memory and lock duration of a long-running insert all push back, so batches of a few hundred to a few thousand tuples are usually the practical sweet spot.
CREATE TABLE track_signal (
id serial PRIMARY KEY,
milepost numeric(6,2) NOT NULL,
aspect text NOT NULL DEFAULT 'clear'
);
INSERT INTO track_signal (milepost, aspect) VALUES
(12.40, 'stop'),
(18.75, 'approach'),
(24.10, 'clear'),
(31.05, 'stop');
SELECT id, milepost, aspect FROM track_signal ORDER BY id;A multi-row VALUES list is one statement constructing one table of rows, so it commits or fails as a whole and pays the per-statement overhead only once.
Worked examples
A duplicate in the last tuple loses all of them
Shows that a constraint violation anywhere in the list leaves the table completely unchanged.
CREATE TABLE crew (
badge int PRIMARY KEY,
name text NOT NULL
);
INSERT INTO crew (badge, name) VALUES
(101, 'Ada'),
(102, 'Grace'),
(101, 'Katherine');
SELECT count(*) AS rows_present FROM crew;Example explained
Line 1The third tuple repeats badge 101, which the primary key index rejects as the statement writes rows.
Line 2The two rows written before it are undone with the statement, because the statement is the unit of atomicity.
Line 3count(*) returns 0, so there is no half-loaded table to clean up before retrying.
Line 4Three separate INSERT statements under autocommit would instead have left Ada and Grace behind.
Letting one row fall back to the column default
Demonstrates the DEFAULT keyword inside a tuple, which is the only way to skip a column for some rows when the column list is shared.
CREATE TABLE shipment (
id serial PRIMARY KEY,
carrier text NOT NULL,
priority int NOT NULL DEFAULT 5
);
INSERT INTO shipment (carrier, priority) VALUES
('Rail North', 1),
('Barge Co', DEFAULT),
('Air Link', 9);
SELECT id, carrier, priority FROM shipment ORDER BY id;Example explained
Line 1The column list (carrier, priority) applies to all three tuples, so every tuple must supply a priority expression.
Line 2DEFAULT in the second tuple asks the engine for the column's declared default, 5, instead of a literal.
Line 3id is not in the column list at all, so serial supplies a value for each tuple as the list is walked.
Line 4Writing NULL there instead would violate NOT NULL and abort all three rows.
Important notes
Limits are real: SQL Server accepts at most 1000 rows in one VALUES clause, and the PostgreSQL wire protocol caps a statement at 65535 bind parameters, so a wide table hits that after roughly a few thousand rows.
SQLite does not accept the DEFAULT keyword inside VALUES; there, group the rows that need the default into a second INSERT whose column list omits that column.
Common mistakes
Pasting tuples from a spreadsheet with two columns swapped in a few rows: if both columns are text the insert succeeds and the wrong values land in the wrong columns with no error at all.
Treating a failed multi-row insert as a partial one, inserting the supposedly missing rows by hand, then rerunning the corrected statement and ending up with duplicates.
Looping tens of thousands of single-row inserts under autocommit instead of grouping them, so every row becomes its own durable transaction and the load takes minutes rather than seconds.
Building the tuple list by concatenating strings of user data, which breaks on any value containing an apostrophe and opens an injection hole.
Try it yourself
Change, predict, then run
Create book(id int primary key, title text not null, copies int not null default 1) and insert four books in one statement, with exactly one row taking the default for copies. Then add a fifth tuple that reuses an existing id, rerun the statement, and use SELECT count(*) to confirm the failed statement inserted nothing.
Open the SQL workspaceCheck your understanding
A single INSERT carrying 500 tuples fails on tuple 499 with a unique key violation, with autocommit on and no explicit transaction. What does the table contain afterwards?
- Nothing new: none of the 500 rows were kept
- The first 498 rows, committed before the failure
- All 500 rows, with the duplicate silently ignored
- 499 rows, because only the offending tuple was skipped
Show answer
Autocommit commits per statement, and the statement failed, so every row it had written is discarded and the table is untouched. The 498-row answer is tempting because it describes what 500 separate INSERT statements would leave behind, which is precisely the partial-load problem a single multi-row statement removes; and no engine skips a violating tuple unless you explicitly ask for conflict handling.