Multi Row INSERT

Definition

A multi-row `INSERT` adds several rows in a single statement, using a comma-separated list of VALUES groups, instead of running one INSERT per row.

How It Works

sql
INSERT INTO employees (id, name, department, salary) VALUES
(10, 'Karan', 'Marketing', 58000),
(11, 'Divya', 'Marketing', 61000),
(12, 'Aman', 'Sales', 54000);

This inserts three rows in one round trip to the database, instead of three separate INSERT statements. The performance difference is substantial for bulk data: each individual statement carries network round-trip and transaction-log overhead, so batching many rows into fewer statements (or a proper bulk-load mechanism, 11.9) is significantly faster than looping one-row-at-a-time inserts in application code.

Edge Cases and Pitfalls

• If ANY row in a multi-row INSERT violates a constraint, the entire statement fails (in the default, non-batched-error-tolerant mode) — none of the rows are inserted, not just the bad one. This is the same all-or-nothing behavior as a single-row insert, just applied to the whole batch.
• Very large multi-row INSERT statements (thousands of rows in one statement) can hit statement-size limits or become unwieldy — a genuine bulk-load tool or LOAD DATA/COPY-style mechanism (11.9) scales better than an enormous hand-written multi-row INSERT.
• All rows in one multi-row INSERT must still individually satisfy every constraint — batching doesn't relax or change any validation rules.

Key Takeaways

• Multi-row INSERT batches several VALUES groups into one statement, reducing round-trip overhead versus one-row-at-a-time inserts.
• It's all-or-nothing: one bad row fails the whole batch, by default.
• For truly large volumes, a dedicated bulk-loading mechanism (11.9) outperforms even a large hand-written multi-row INSERT.