How to Insert Multiple Rows in a Single SQL Statement

Posted by Kyle Hankinson November 3, 2022

---

Inserting rows one at a time is slow. Every major database supports inserting multiple rows in a single statement, but Oracle has its own syntax.

Standard Multi-Row INSERT

Works in MySQL, PostgreSQL, SQL Server, and SQLite:

INSERT INTO products (name, price, category)
VALUES
    ('Widget', 9.99, 'hardware'),
    ('Gadget', 24.99, 'electronics'),
    ('Gizmo', 14.99, 'hardware'),
    ('Doohickey', 4.99, 'accessories');

This is significantly faster than four separate INSERT statements because it requires only one round trip to the database.

Oracle: INSERT ALL

Oracle does not support multi-row VALUES syntax. Use INSERT ALL instead:

INSERT ALL
    INTO products (name, price, category) VALUES ('Widget', 9.99, 'hardware')
    INTO products (name, price, category) VALUES ('Gadget', 24.99, 'electronics')
    INTO products (name, price, category) VALUES ('Gizmo', 14.99, 'hardware')
    INTO products (name, price, category) VALUES ('Doohickey', 4.99, 'accessories')
SELECT 1 FROM DUAL;

The SELECT 1 FROM DUAL at the end is required syntax.

INSERT ... SELECT

Copy data from one table to another:

INSERT INTO products_archive (name, price, category)
SELECT name, price, category
FROM products
WHERE discontinued = 1;

This works identically in all databases.

Row Limits

There are practical limits to how many rows you can insert in one statement:

Database	Limit
MySQL	Limited by max_allowed_packet (default 64MB)
PostgreSQL	No hard limit (memory-dependent)
SQL Server	1,000 rows per VALUES clause
SQLite	Limited by SQLITE_MAX_VARIABLE_NUMBER (default 999 per statement)

For SQL Server, batch your inserts into groups of 1,000:

INSERT INTO products (name, price) VALUES
    ('Item1', 1.00), ('Item2', 2.00), ... -- up to 1000 rows
;
INSERT INTO products (name, price) VALUES
    ('Item1001', 1.00), ... -- next 1000 rows
;

Performance Comparison

Inserting 10,000 rows:

• 10,000 single-row INSERTs: Slow (10,000 network round trips)
• Multi-row INSERT in batches of 1,000: Fast (10 round trips)
• LOAD DATA / COPY / BULK INSERT: Fastest (database-specific bulk loaders)

For very large data loads, use the database's native bulk import:

-- MySQL
LOAD DATA INFILE '/path/to/data.csv' INTO TABLE products;

-- PostgreSQL
COPY products FROM '/path/to/data.csv' CSV HEADER;

-- SQL Server
BULK INSERT products FROM '/path/to/data.csv' WITH (FORMAT = 'CSV');

INSERT with RETURNING (PostgreSQL)

PostgreSQL can return the inserted rows, which is useful for getting auto-generated IDs:

INSERT INTO products (name, price)
VALUES ('Widget', 9.99), ('Gadget', 24.99)
RETURNING id, name;

SQL Server has similar functionality with OUTPUT:

INSERT INTO products (name, price)
OUTPUT inserted.id, inserted.name
VALUES ('Widget', 9.99), ('Gadget', 24.99);

---