INSERT Multiple Rows

Adding a whole IPL squad one INSERT at a time is slow and repetitive. SQL lets you add many rows in one statement — just keep adding value lists.

Definition

A multi-row INSERT adds several new rows with a single INSERT statement by supplying multiple value lists after one VALUES keyword, separated by commas. Each value list (in brackets) becomes one row.

• One statement — one INSERT INTO, one VALUES.
• Multiple value lists — (…), (…), (…).
• All or nothing — if any one row breaks a rule (duplicate key, NULL in a NOT NULL column), the whole statement fails and none of the rows are added.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES
    (value1, value2, ...),   -- row 1
    (value1, value2, ...),   -- row 2
    (value1, value2, ...);   -- row 3 (no comma after the last one)

From the course slide:

INSERT INTO players (id, name, team, country, runs)
VALUES (1, 'Virat Kohli', 'RCB', 'India', 750),
       (2, 'Rohit Sharma', 'MI', 'India', 500);

Multiple inserts: just add another list of values.

Example — 5 new rows at once

Before: the table has only Virat Kohli.

INSERT INTO players VALUES
    (2, 'Rohit Sharma',   'MI',  'India',     500),
    (3, 'Jos Buttler',    'RR',  'England',   850),
    (4, 'MS Dhoni',       'CSK', 'India',     350),
    (5, 'David Warner',   'DC',  'Australia', 700),
    (6, 'Jasprit Bumrah', 'MI',  'India',      20);

SELECT * FROM players;

After — 5 new rows at once:

Step by step: 1️⃣ One INSERT, many rows → 2️⃣ separate the value lists with commas. SQL Server reports (5 rows affected).

Note: This example has no column list, so we must give a value for EVERY column, in table order (id, name, team, country, runs). It works, but writing the column list is still the safer habit.

Why multi-row is better than many single INSERTs

Common mistakes

• ❌ Writing VALUES before every row: VALUES (…), VALUES (…). ✅ VALUES appears once.
• ❌ Forgetting the comma between rows, or leaving one after the last row. ✅ (…), (…), (…);
• ❌ Rows with different numbers of values. ✅ Every list must match the column list.
• ❌ One duplicate id in the batch and expecting the others to be saved. ✅ The whole statement is rejected — fix the duplicate and re-run.

SQL Server vs MySQL / PostgreSQL

All three support multi-row VALUES. In SQL Server a single VALUES list may contain at most 1,000 rows — for more, use several INSERTs, INSERT … SELECT, or bulk-load tools (BULK INSERT, bcp).

Interview tip

Q: If one of the rows in a multi-row INSERT violates the primary key, how many rows are inserted? A: Zero. A single INSERT statement is atomic — it succeeds completely or fails completely.