SQL / INSERTING, UPDATING, AND DELETING ROWS
Copying rows with INSERT INTO SELECT
Copy rows between tables with INSERT INTO ... SELECT, filtering, reshaping and adding literal columns while the query decides how many rows land.
What you will learn
- Copy a filtered subset of rows with INSERT INTO target (cols) SELECT ... FROM source
- Line up SELECT expressions with the target column list by position, never by name
- Fill target-only columns with literals or expressions inside the SELECT list
- Check the reported count: the SELECT decides how many rows are inserted, even zero
Understanding Copying rows with INSERT INTO SELECT
INSERT INTO ... SELECT swaps the VALUES list for a query. The database runs the SELECT and feeds every row of that result set into the target table, so the data never travels out to your client and back again. The consequence worth holding on to is that the query, not the source table, decides the amount of work: ten million matching rows means ten million inserted rows, and an empty result means a statement that succeeds having inserted nothing.
The link between query and table is positional. The target column list is the contract: the first expression in the SELECT goes into the first named column, the second into the second, and so on. Names and aliases in the SELECT are ignored entirely, so SELECT city AS name still lands in whichever column occupies that position. When two columns share a type, a swapped pair raises no error at all, just wrong rows, which is exactly why leaning on SELECT * with no target column list is fragile.
Because the source is an ordinary query, everything a query can do is available while copying: WHERE to pick rows, JOIN to draw columns from several tables, GROUP BY to insert summarised rows, literals and expressions for columns the source has no equivalent for, UNION ALL to stack sources. The target's own rules still apply, including NOT NULL, defaults for columns you left out of the list, and primary and foreign keys, and one violation aborts the whole statement and leaves the target untouched. The query's input is also fixed at the moment the statement begins, so INSERT INTO t SELECT * FROM t doubles the rows and stops instead of reading its own output forever.
Copying is therefore a shaping operation, not a clone: only the values named in the SELECT reach the target, and nothing about the source's indexes, defaults or constraints comes with them.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer TEXT,
status TEXT,
amount INTEGER
);
CREATE TABLE orders_archive (
id INTEGER PRIMARY KEY,
customer TEXT,
amount INTEGER
);
INSERT INTO orders (id, customer, status, amount) VALUES
(1, 'Ada', 'shipped', 120),
(2, 'Grace', 'pending', 45),
(3, 'Linus', 'shipped', 100),
(4, 'Ada', 'cancelled', 15);
INSERT INTO orders_archive (id, customer, amount)
SELECT id, customer, amount
FROM orders
WHERE status = 'shipped';
SELECT id, customer, amount FROM orders_archive ORDER BY id;INSERT INTO ... SELECT pipes a query's result set into a table, matching columns by position and inserting exactly as many rows as the query returns.
Worked examples
Summarising while copying
A grouped query as the source, plus a literal for a column the source table does not have.
CREATE TABLE sales (region TEXT, units INTEGER);
INSERT INTO sales (region, units) VALUES
('north', 3), ('south', 7), ('north', 5), ('east', 2);
CREATE TABLE region_totals (region TEXT, total_units INTEGER, source TEXT);
INSERT INTO region_totals (region, total_units, source)
SELECT region, SUM(units), 'sales'
FROM sales
GROUP BY region;
SELECT region, total_units, source FROM region_totals ORDER BY region;Example explained
Line 1SUM(units) sits in the second position of the SELECT list, so it lands in total_units, the second column named in the target list.
Line 2The literal 'sales' supplies a value for a target column that has no counterpart in the sales table.
Line 3GROUP BY collapses four source rows into three result rows, and three is what gets inserted: the row count follows the query, not the source table.
Line 4north shows 8 because the two north rows were summed before insertion, not stored separately.
Positional matching, not name matching
A copy that succeeds without error while storing every value in the wrong column.
CREATE TABLE people (name TEXT, city TEXT);
INSERT INTO people (name, city) VALUES ('Ada', 'London'), ('Grace', 'New York');
CREATE TABLE contacts (city TEXT, name TEXT);
INSERT INTO contacts (city, name)
SELECT name, city FROM people;
SELECT city, name FROM contacts ORDER BY city;Example explained
Line 1The target list starts with city, and the SELECT list starts with name, so each person's name is stored as their city.
Line 2Both columns are TEXT, so there is nothing for the engine to reject; the statement reports two rows inserted.
Line 3Matching the words city and name between the two statements is meaningless; rewriting the query as SELECT city, name FROM people is the fix.
Line 4ORDER BY city sorts on the misplaced names, which is the visible symptom of the swap.
Making a re-run insert nothing
A NOT EXISTS guard against the target table so running the copy twice does not duplicate rows.
CREATE TABLE tickets (id INTEGER PRIMARY KEY, title TEXT, closed INTEGER);
INSERT INTO tickets (id, title, closed) VALUES
(1, 'login fails', 1),
(2, 'slow report', 0),
(3, 'typo on page', 1);
CREATE TABLE tickets_closed (id INTEGER PRIMARY KEY, title TEXT);
INSERT INTO tickets_closed (id, title)
SELECT t.id, t.title
FROM tickets t
WHERE t.closed = 1
AND NOT EXISTS (SELECT 1 FROM tickets_closed c WHERE c.id = t.id);
INSERT INTO tickets_closed (id, title)
SELECT t.id, t.title
FROM tickets t
WHERE t.closed = 1
AND NOT EXISTS (SELECT 1 FROM tickets_closed c WHERE c.id = t.id);
SELECT id, title FROM tickets_closed ORDER BY id;Example explained
Line 1The first statement copies the two closed tickets, skipping ticket 2 because closed is 0.
Line 2NOT EXISTS queries the target table from inside the same statement, filtering out rows that are already there.
Line 3The second, identical statement reports INSERT 0 0: the guard did the remembering, because INSERT ... SELECT itself keeps no record of earlier runs.
Line 4Without that guard the second run would fail on the primary key of tickets_closed, and on a table with no key it would quietly store both rows twice.
Important notes
An empty result set is not an error: the statement succeeds having inserted nothing, so read the reported count instead of assuming a copy happened.
Row counts here are printed the way psql reports them, as INSERT 0 2; other clients word it differently, but every one of them tells you how many rows the SELECT produced.
Common mistakes
Writing INSERT INTO archive SELECT * FROM orders: as soon as either table gains or reorders a column, values slide into neighbouring columns or the statement dies with a type error, and nothing in the code records which order was intended.
Expecting aliases to route values, so SELECT amount AS total is assumed to fill a column named total; only position decides, and the value silently lands in a different column.
Running the copy a second time to catch new rows, which re-copies the old ones as well; with no unique constraint on the target you get two of everything and no error to warn you.
Forgetting the target's NOT NULL columns when the source has nothing to put there, which aborts the entire statement and inserts none of the rows, not just the offending one.
Try it yourself
Change, predict, then run
Create a books table with five rows including a year_published column, plus an empty classics(title TEXT, year_published INTEGER, tag TEXT), then copy only the books published before 1950 into classics, filling tag with the literal 'classic'. Confirm the reported insert count equals the number of pre-1950 books you created.
Open the SQL workspaceCheck your understanding
A table log(msg TEXT) holds five rows and has no constraints or indexes. You run INSERT INTO log SELECT msg FROM log; What is the result?
- log ends up with ten rows, because the SELECT reads the table as it stood when the statement began
- The statement never finishes, because each inserted row is immediately re-read by the SELECT
- The statement fails, because a table cannot be both the target and the source of one INSERT
- log still holds five rows, because the engine skips rows that already exist in the target
Show answer
The query's input is fixed at the start of the statement, so it sees the original five rows, inserts five copies and stops at ten; that stability is what makes a self-copy safe. The never-finishes option assumes the SELECT keeps re-reading the growing table, which set-based INSERT semantics rule out, and the last option assumes deduplication that INSERT never performs on its own.