Inserting Multiple Rows

Adding one row at a time is fine for a quick edit, but real applications frequently need to insert many rows at once — a bulk import, a batch job, seed data for a new environment. SQL lets you supply several sets of values in a single INSERT statement instead of writing one statement per row.

Multi-row VALUES syntax

List multiple parenthesized groups after VALUES, separated by commas. Each group becomes one new row:

Inserting three rows in one statement

SQL

INSERT INTO employees (first_name, last_name, department)
VALUES
  ('Ada', 'Lovelace', 'Engineering'),
  ('Grace', 'Hopper', 'Engineering'),
  ('Katherine', 'Johnson', 'Mathematics');

This inserts all three rows as a single unit of work. If your table has a RETURNING clause available, it will return one row for every row inserted, not just the last one.

Why this is faster

Running a thousand separate INSERT INTO ... VALUES (...) statements is dramatically slower than running one statement with a thousand value groups, even though both insert the same data. Each individual statement carries overhead that a multi-row insert lets you pay only once:

Fewer round trips, less overhead

Every statement your application sends to the database usually involves a network round trip, SQL parsing, and query planning. Batch multiple rows into one INSERT and you pay that cost once instead of once per row. Many databases also do less transaction-log and write-ahead-log bookkeeping for one large statement than for many small ones, and if each single-row insert was wrapped in its own transaction, batching also avoids repeated commit overhead. For bulk loads of thousands or millions of rows, this difference is not minor — it is often the difference between seconds and hours.

Very large batches

Extremely large batches (hundreds of thousands of rows) can hit statement size limits or use a lot of memory building the statement. In practice, bulk loads are often chunked into batches of a few hundred or few thousand rows, or done with a database-specific bulk load tool (such as PostgreSQL’s COPY) rather than one giant INSERT.

INSERT INTO ... SELECT

When the rows you want to insert already exist somewhere else in the database — another table, a filtered subset, or the result of joining and transforming several tables — you don’t need to pull the data out and type it back in. INSERT INTO ... SELECT lets you copy the result of a query directly into a table:

Copying rows from another table

SQL

INSERT INTO archived_employees (id, first_name, last_name, department)
SELECT id, first_name, last_name, department
FROM employees
WHERE is_active = FALSE;

The column list after the table name still applies, and the columns selected must line up with it in number and compatible type. This pattern is extremely common for archiving old records, populating a summary or reporting table, or seeding a new table from an existing one — all without ever pulling the data out of the database.

Inserting a transformed/aggregated result

SQL

INSERT INTO department_headcount (department, employee_count)
SELECT department, COUNT(*)
FROM employees
GROUP BY department;