How do I insert multiple rows in one SQL statement?

List several comma-separated VALUES tuples in a single INSERT: INSERT INTO products (name, price) VALUES ('Mouse', 25), ('Monitor', 199). One multi-row insert is much faster than many separate statements because it is a single round-trip and transaction.

What is an UPSERT in SQL?

An upsert inserts a new row, or updates the existing one if it would violate a unique constraint. In PostgreSQL you write it with ON CONFLICT, choosing DO NOTHING to ignore duplicates or DO UPDATE SET ... to modify the existing row.

How do I get the id of a row I just inserted?

In PostgreSQL, add a RETURNING clause to the INSERT: INSERT INTO users (name) VALUES ('Ada') RETURNING id. It returns columns from the newly inserted row, so you do not need a separate query to look up the generated id.

Beginner

SQL INSERT: Single Rows, Bulk, INSERT…SELECT & UPSERT

Adding data is more than one row at a time. Learn single and bulk inserts, copying rows with INSERT…SELECT, RETURNING generated ids, and upserts with ON CONFLICT.

INSERT adds rows to a table. It looks trivial, but doing it well — naming columns, inserting in bulk, copying from a query, and handling conflicts — separates fragile scripts from solid ones. Keep the SQL cheat sheet open as you read.

Inserting a Single Row

Always list the columns you are inserting into. It makes the statement readable and, crucially, keeps it working when someone later adds or reorders columns in the table.

INSERT INTO users (name, email)
VALUES ('Ada Lovelace', 'ada@example.com');

Inserting Many Rows

Insert several rows in one statement by listing multiple VALUES tuples. A single multi-row INSERT is far faster than many separate statements, because it is one round-trip and one transaction.

INSERT INTO products (name, price) VALUES
  ('Keyboard', 49.00),
  ('Mouse',   25.00),
  ('Monitor', 199.00);

Defaults & Generated Columns

Columns you omit take their DEFAULT (or NULL if none). Auto-generated identity columns and timestamp defaults mean you usually do not supply an id or created_at at all — let the database fill them.

-- id and created_at are generated; just provide the real data
INSERT INTO orders (user_id, total)
VALUES (1, 250.00);

-- Be explicit about taking a default
INSERT INTO orders (user_id, total, status)
VALUES (1, 250.00, DEFAULT);

INSERT…SELECT (Copying)

You can feed an INSERT from a SELECT instead of literal values — the standard way to copy or transform rows, archive old data, or seed a table from another.

-- Archive completed orders into a history table
INSERT INTO orders_archive (id, user_id, total, created_at)
SELECT id, user_id, total, created_at
FROM orders
WHERE status = 'completed';

RETURNING the New Row

PostgreSQL’s RETURNING clause hands back columns from the rows you just inserted — most often the generated id, so you do not need a second query to find it.

INSERT INTO users (name, email)
VALUES ('Grace Hopper', 'grace@example.com')
RETURNING id, created_at;

UPSERT with ON CONFLICT

An upsert means "insert, or update if it already exists". PostgreSQL spells this ON CONFLICT: name the unique column that might collide, then decide whether to do nothing or update the existing row.

-- Insert, or bump the count if this email already exists
INSERT INTO signups (email, visits)
VALUES ('ada@example.com', 1)
ON CONFLICT (email)
DO UPDATE SET visits = signups.visits + 1;

-- Or simply ignore duplicates
INSERT INTO tags (name) VALUES ('sql')
ON CONFLICT (name) DO NOTHING;

Common Mistakes & Tips

Omitting the column list — the insert breaks when the table’s columns change
Running many single-row inserts in a loop instead of one multi-row insert
Supplying a value for an auto-generated id column and causing conflicts
Forgetting ON CONFLICT needs a matching unique constraint to work
Reading rows to the client just to insert them elsewhere — use INSERT…SELECT instead

INSERT is where data enters your database, so getting it right matters. Practice in the writable sandbox with bulk inserts, INSERT…SELECT, and upserts.