How to insert multiple rows in one SQL statement

1VALUES (…), (…), (…): many rows in one INSERTRecommended

After the column list, write VALUES once and then one parenthesised row per line, with a comma between rows and a semicolon only at the very end. Each row lists its values in the same order as the column list. SELECT changes() is SQLite's count of the rows the last statement touched, which confirms that one statement added all three.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT
);

-- One INSERT, three rows: a parenthesised list per row, separated by commas
INSERT INTO users (id, name, city)
VALUES
    (1, 'Ada', 'London'),
    (2, 'Grace', 'New York'),
    (3, 'Linus', 'Helsinki');

SELECT changes() AS rows_added;

SELECT * FROM users;

rows_added is 3, and the table holds Ada, Grace and Linus. The same statement runs unchanged on PostgreSQL, MySQL and SQL Server (2008 and later). SQLite has accepted it since 3.7.11 (2012); this page runs 3.53.4. In MySQL, ROW_COUNT() plays the part of changes(), and SQL Server prints (3 rows affected) by itself. Oracle accepts multi-row VALUES only from 23ai; older versions use INSERT ALL or the UNION ALL form in section 5.

2Name the columns, let the rest take their defaults

You only have to list the columns you are filling. Every column you leave out gets its DEFAULT, or NULL if it has none, and an INTEGER PRIMARY KEY gets the next id. The catch with a multi-row insert is that every row fills the same columns. When one row needs a value the others don't, either add the column and write the default out for the other rows, or split the rows into two statements, as here.

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'todo',
    due TEXT
);

-- Name only the columns you fill: id, status and due get their defaults
INSERT INTO tasks (title)
VALUES ('Write tests'), ('Fix login'), ('Ship v2');

-- Every row fills the same columns, so spell out the value where rows differ
INSERT INTO tasks (title, status, due)
VALUES
    ('Plan sprint', 'done', '2026-03-14'),
    ('Review PR', 'todo', NULL);

SELECT * FROM tasks;

The first three tasks got ids 1 to 3, the default status todo and a NULL due date. In the second statement, Review PR repeats 'todo' by hand because its row has to fill status too. PostgreSQL, MySQL and SQL Server let you write the keyword DEFAULT in that slot instead, as in ('Alan', DEFAULT); all three ran it. SQLite doesn't: it rejects the statement with near "DEFAULT": syntax error. Every row must also have the same number of values as the column list, or SQLite refuses the whole statement with 3 values for 2 columns or all VALUES must have the same number of terms.

3RETURNING: see the ids of the rows you inserted

When the database assigns the ids, you usually need them back, for example to insert child rows that point at the new parents. Add RETURNING and a column list to the end of the INSERT, and it gives back one row per inserted row, like a SELECT. SQLite has had it since 3.35. The table already holds one tag, so the new rows start at id 2.

CREATE TABLE tags (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);
INSERT INTO tags (name) VALUES ('sql');

-- RETURNING (SQLite 3.35+) gives back one row per inserted row
INSERT INTO tags (name)
VALUES ('joins'), ('indexes'), ('json')
RETURNING id, name;

The insert returns ids 2, 3 and 4, each next to its name. PostgreSQL uses the same RETURNING clause. SQL Server puts an OUTPUT clause between the column list and VALUES: INSERT INTO users (name) OUTPUT INSERTED.id, INSERTED.name VALUES (…), (…). MySQL has no RETURNING (8.4 rejects it with a syntax error); after a multi-row insert, LAST_INSERT_ID() gives the id of the first new row and ROW_COUNT() the number of rows. More on single rows in get the id of an inserted row.

4Many single INSERTs inside one transaction

Sometimes rows reach you one at a time: a loop in your application, a CSV read line by line, a script of generated statements. Run each INSERT on its own and the database treats each one as a separate transaction, committing (and on a file database, syncing to disk) after every row. That per-row commit is usually what makes bulk loads slow. Wrap the batch in BEGIN … COMMIT and the database commits once for the whole lot. As a bonus, the batch becomes all-or-nothing: ROLLBACK undoes every row since BEGIN.

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    kind TEXT NOT NULL,
    at TEXT NOT NULL
);

-- Many single-row INSERTs, one transaction, one commit
BEGIN;
INSERT INTO events (kind, at) VALUES ('login', '2026-03-14 09:30:00');
INSERT INTO events (kind, at) VALUES ('view', '2026-03-14 09:31:10');
INSERT INTO events (kind, at) VALUES ('logout', '2026-03-14 09:45:02');
COMMIT;

-- ROLLBACK throws away every INSERT since BEGIN
BEGIN;
INSERT INTO events (kind, at) VALUES ('test', '2026-03-14 10:00:00');
INSERT INTO events (kind, at) VALUES ('test', '2026-03-14 10:00:01');
ROLLBACK;

SELECT kind, at FROM events;

The three committed events are there; the two test rows are gone because their transaction was rolled back. This page uses an in-memory database, so it can't show the speed difference, but on a real file or server database the gap between autocommit and one transaction grows with every row. In application code, combine this with a prepared statement that you run once per row, or send the rows in multi-row VALUES chunks inside the transaction. PostgreSQL and MySQL accept BEGIN as written (MySQL also has START TRANSACTION), and SQL Server uses BEGIN TRANSACTION … COMMIT; all three ran it.

5The old form: INSERT … SELECT … UNION ALL SELECT …

Before multi-row VALUES reached every database (SQLite before 3.7.11, SQL Server before 2008, Oracle before 23ai), the usual trick was to build the rows as a query. Each row is a SELECT of literal values, the rows are joined with UNION ALL, and INSERT INTO … SELECT stores the result. It still works everywhere, so you will meet it in older scripts and answers. Use UNION ALL, not plain UNION: the second statement shows why.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT
);

-- The old form: one SELECT per row, glued together with UNION ALL
INSERT INTO users (id, name, city)
SELECT 1, 'Ada', 'London'
UNION ALL SELECT 2, 'Grace', 'New York'
UNION ALL SELECT 3, 'Linus', 'Helsinki';

-- Plain UNION removes duplicate rows first: two rows in, one row stored
INSERT INTO users (name, city)
SELECT 'Alan', 'London'
UNION SELECT 'Alan', 'London';

SELECT * FROM users;

The UNION ALL insert stores the same three rows as section 1. The UNION insert offered Alan twice but stored him once (id 4), because UNION removes duplicate rows before the insert sees them; that silently loses data when two real rows happen to be equal. This form has a size limit in SQLite: a compound SELECT of 500 terms ran, but 501 failed with too many terms in compound SELECT. A 5,000-row VALUES list ran without complaint, which is one more reason to prefer section 1. In Oracle, each SELECT needs FROM dual.

6Which should you use?

7When one row fails, the whole statement is rolled back

A multi-row INSERT is a single statement, and a statement either succeeds completely or changes nothing. If any row breaks a constraint (a duplicate key, a NULL in a NOT NULL column, a failed CHECK), the rows before it in the same list are undone too. Here the third row repeats an email that is already in the table.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);
INSERT INTO users (email) VALUES ('[email protected]');

-- The third row repeats an email, so the whole statement fails
INSERT INTO users (email)
VALUES ('[email protected]'), ('[email protected]'), ('[email protected]');

-- Neither grace nor linus was kept: only the earlier row is there
SELECT * FROM users;

SQLite reports UNIQUE constraint failed: users.email, and the table still holds only [email protected]: grace and linus were valid, but they went down with the statement. PostgreSQL, MySQL and SQL Server behave the same way; on each of them the table kept only its first row after the same failing insert. To keep the good rows and skip the duplicates, add ON CONFLICT DO NOTHING (SQLite 3.24+ and PostgreSQL) or write INSERT IGNORE in MySQL; both stored grace and linus and skipped ada. For updating the existing row instead, see insert or update if it exists.