INSERT with Multiple Rows
Insert one or many rows with a single statement, and use INSERT ... SELECT to copy data between tables.
INSERT with Multiple Rows is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Single-Row INSERT
The simplest form names columns and supplies values:
INSERT INTO users (email, full_name)
VALUES ('alice@example.com', 'Alice Adams');Multi-Row INSERT
Insert many rows in one statement. Faster than many round-trips:
INSERT INTO users (email, full_name) VALUES
('alice@example.com', 'Alice Adams'),
('bob@example.com', 'Bob Brown'),
('carol@example.com', 'Carol Chen');Why Multi-Row Is Faster
Fewer round-trips to the database, fewer parsing/planning cycles, one WAL flush. For bulk inserts of 100s of rows, multi-VALUES is 5–10x faster than one-by-one.
Default Values
Omit a column to use its DEFAULT (or NULL):
INSERT INTO orders (user_id, total)
VALUES (1, 99.50);
-- id, created_at, status filled by DEFAULTsExplicit DEFAULT
Or use the DEFAULT keyword:
INSERT INTO orders (id, user_id, total, status)
VALUES (DEFAULT, 1, 99.50, DEFAULT);RETURNING
Get back the columns of the inserted row(s) without a second query (PostgreSQL):
INSERT INTO users (email, full_name)
VALUES ('alice@example.com', 'Alice Adams')
RETURNING id, created_at;INSERT … SELECT
Copy or transform rows from another table:
INSERT INTO archive_orders (id, user_id, total)
SELECT id, user_id, total
FROM orders
WHERE created_at < NOW() - INTERVAL '5 years';Bulk Loading with COPY
For tens of thousands of rows or more, use the COPY command — by far the fastest path:
COPY users (email, full_name) FROM '/tmp/users.csv' (FORMAT csv, HEADER true);
-- Or from STDIN in a client:
COPY users (email, full_name) FROM STDIN;
alice@example.com\tAlice
bob@example.com\tBob
\.Avoid Inserting in Big Transactions Without Reason
One transaction = one atomic block. If a million-row import fails halfway, the whole thing rolls back. Sometimes you want that; sometimes you batch by 10k rows.
INSERT Performance Tips
For large inserts:
- Drop secondary indexes, insert, recreate them
- Disable triggers / FK checks if safe
- Use COPY when possible
- Adjust
maintenance_work_memfor index rebuilds
Conflict Handling Preview
To insert "if new, otherwise update", you use INSERT ... ON CONFLICT — covered next lesson.
Recap
INSERT is straightforward; multi-row VALUES and INSERT … SELECT cover the daily cases. RETURNING avoids extra round-trips. For bulk, use COPY.
Quick Check
Which is the fastest way to load 100,000 rows from a CSV file into PostgreSQL?
Frequently asked questions
Is the “INSERT with Multiple Rows” lesson free?
Yes — the full text of “INSERT with Multiple Rows” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “INSERT with Multiple Rows”?
Insert one or many rows with a single statement, and use INSERT ... SELECT to copy data between tables. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “INSERT with Multiple Rows” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.