INSERT INTO … SELECT copies rows from one table into another in a single statement, with no need to retype them as VALUES. It’s how you archive old rows or fill a summary table. In this article I’ll walk you through how it works, from a straight copy to skipping the rows you already have.
TLDR
INSERT INTO target (columns) SELECT columns FROM source WHERE …;inserts one row for every row theSELECTreturns.- The
SELECTmust return as many columns as the column list names, in the same order. Names don’t have to match. - The
SELECTcan calculate and group, so it fills summary tables too. - SQLite has no
SELECT … INTO new_table. UseCREATE TABLE new_table AS SELECT …instead. - To skip rows that already exist, add
WHERE NOT EXISTSto theSELECT. To update them instead, addON CONFLICT.
What is INSERT INTO SELECT?
INSERT INTO … SELECT is the form of INSERT that takes its rows from a query instead of a VALUES list. The SQLite INSERT docs put it this way: “A new entry is inserted into the table for each row of data returned by executing the SELECT statement.” Any SELECT works there, joins and GROUP BY included.
For a single row with literal values, the regular INSERT with VALUES is simpler.
SQLite INSERT INTO SELECT syntax
INSERT INTO target_table (column1, column2, ...)
SELECT expression1, expression2, ...
FROM source_table
[WHERE condition];| Part | Rule |
|---|---|
| Column list | Optional. Without it, the SELECT must return one value for every column in the target table |
SELECT list | Same number of values as the column list, matched by position, not by name |
WHERE, GROUP BY, ORDER BY, LIMIT | All allowed. They decide which rows get inserted |
ON CONFLICT | Allowed, but the SELECT then needs a WHERE clause (see edge cases) |
How to copy rows from one table to another
To show you how this works, I will use a small orders table and an empty orders_archive table with the same columns:
sqlite> CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, product TEXT NOT NULL, qty INTEGER NOT NULL, price REAL NOT NULL, ordered_on TEXT NOT NULL);
sqlite> INSERT INTO orders (customer, product, qty, price, ordered_on) VALUES ('ada', 'Widget', 2, 9.99, '2026-10-01'), ('linus', 'Gadget', 1, 19.99, '2026-10-01'), ('ada', 'Gizmo', 3, 14.99, '2026-10-02'), ('grace', 'Widget', 5, 9.99, '2026-10-02');
sqlite> CREATE TABLE orders_archive (id INTEGER PRIMARY KEY, customer TEXT, product TEXT, qty INTEGER, price REAL, ordered_on TEXT);Step 1: Write the SELECT that returns the rows you want
Start with the query on its own, so you can check the rows before you insert anything. Here it’s every order from 1 October:
sqlite> SELECT * FROM orders WHERE ordered_on = '2026-10-01';
id customer product qty price ordered_on
-- -------- ------- --- ----- ----------
1 ada Widget 2 9.99 2026-10-01
2 linus Gadget 1 19.99 2026-10-01Step 2: Put INSERT INTO in front of it
Because orders_archive has the same columns in the same order, SELECT * lines up without a column list:
sqlite> INSERT INTO orders_archive SELECT * FROM orders WHERE ordered_on = '2026-10-01';
sqlite> SELECT * FROM orders_archive;
id customer product qty price ordered_on
-- -------- ------- --- ----- ----------
1 ada Widget 2 9.99 2026-10-01
2 linus Gadget 1 19.99 2026-10-01Only the two orders from 1 October were copied, and they kept their id values.
Examples
These examples all read from the same orders table.
Insert into specific columns
Name the target columns when the tables differ. Here the SELECT builds one row per customer with their first order date, and id is filled in automatically:
sqlite> CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, first_order TEXT);
sqlite> INSERT INTO customers (name, first_order) SELECT customer, min(ordered_on) FROM orders GROUP BY customer;
sqlite> SELECT * FROM customers;
id name first_order
-- ----- -----------
1 ada 2026-10-01
2 grace 2026-10-02
3 linus 2026-10-01Insert calculated values
The SELECT list can hold any expression. Each order becomes an invoice line with a total and a total with 20% tax:
sqlite> CREATE TABLE invoice_lines (order_id INTEGER, line_total REAL, with_tax REAL);
sqlite> INSERT INTO invoice_lines (order_id, line_total, with_tax) SELECT id, qty * price, round(qty * price * 1.2, 2) FROM orders;
sqlite> SELECT * FROM invoice_lines;
order_id line_total with_tax
-------- ---------- --------
1 19.98 23.98
2 19.99 23.99
3 44.97 53.96
4 49.95 59.94Insert aggregated values into a summary table
With GROUP BY, each group becomes one inserted row:
sqlite> CREATE TABLE daily_sales (day TEXT PRIMARY KEY, orders INTEGER, revenue REAL);
sqlite> INSERT INTO daily_sales (day, orders, revenue) SELECT ordered_on, count(*), round(sum(qty * price), 2) FROM orders GROUP BY ordered_on;
sqlite> SELECT * FROM daily_sales;
day orders revenue
---------- ------ -------
2026-10-01 2 39.97
2026-10-02 2 94.92Copy rows from another database file
ATTACH a second database, then select from it with its schema name. Here other.db holds one order from 2025 in a legacy_orders table:
$ sqlite3 other.db "CREATE TABLE legacy_orders (customer TEXT, product TEXT, qty INTEGER, price REAL, ordered_on TEXT); INSERT INTO legacy_orders VALUES ('ada', 'Widget', 1, 8.99, '2025-12-24');"
sqlite> ATTACH DATABASE 'other.db' AS old; INSERT INTO orders (customer, product, qty, price, ordered_on) SELECT customer, product, qty, price, ordered_on FROM old.legacy_orders; SELECT customer, product, ordered_on FROM orders WHERE ordered_on < '2026-01-01';
customer product ordered_on
-------- ------- ----------
ada Widget 2025-12-24See the inserted rows with RETURNING
Since SQLite 3.35.0, RETURNING lists the rows an INSERT added (RETURNING):
sqlite> INSERT INTO orders_archive SELECT * FROM orders WHERE ordered_on = '2026-10-02' RETURNING id, customer;
id customer
-- --------
3 ada
4 graceCreate a new table from a SELECT
SQLite doesn’t support SELECT … INTO new_table, which SQL Server uses:
sqlite> SELECT * INTO orders_copy FROM orders;
Error: in prepare, near "INTO": syntax error
SELECT * INTO orders_copy FROM orders;
^--- error hereCREATE TABLE … AS SELECT creates the table and fills it in one statement (CREATE TABLE AS):
sqlite> CREATE TABLE big_orders AS SELECT id, customer, qty * price AS total FROM orders WHERE qty * price > 30;
sqlite> SELECT * FROM big_orders;
id customer total
-- -------- -----
3 ada 44.97
4 grace 49.95The new table gets its column names from the SELECT, but no primary key or other constraints. See SQLite CREATE TABLE to define those yourself.
Edge cases
The rules below each come from a statement that failed in the test run.
The number of columns must match
sqlite> INSERT INTO daily_sales (day, orders) SELECT ordered_on, count(*), sum(qty) FROM orders GROUP BY ordered_on;
Error: in prepare, 3 values for 2 columnsThe column list names two columns, and the SELECT returns three values.
Duplicate keys stop the whole statement
customers.name is UNIQUE, so copying every customer name again fails:
sqlite> INSERT INTO customers (name) SELECT customer FROM orders;
Error: stepping, UNIQUE constraint failed: customers.name (19)There are three ways around it. The first is a WHERE NOT EXISTS that leaves out the rows already in the target. Only the new customer, hedy, is inserted:
sqlite> INSERT INTO orders (customer, product, qty, price, ordered_on) VALUES ('hedy', 'Widget', 1, 9.99, '2026-10-03');
sqlite> INSERT INTO customers (name, first_order) SELECT o.customer, min(o.ordered_on) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.name = o.customer) GROUP BY o.customer;
sqlite> SELECT * FROM customers;
id name first_order
-- ----- -----------
1 ada 2026-10-01
2 grace 2026-10-02
3 linus 2026-10-01
4 hedy 2026-10-03The second is INSERT OR IGNORE, which skips any row that breaks a constraint. Here it inserted nothing, because every customer already existed:
sqlite> INSERT OR IGNORE INTO customers (name) SELECT customer FROM orders;
sqlite> SELECT changes() AS rows_inserted, count(*) AS customers FROM customers;
rows_inserted customers
------------- ---------
0 4The third is an upsert, which updates the existing row instead. The two existing days keep their values, and the new days are added:
sqlite> INSERT INTO orders (customer, product, qty, price, ordered_on) VALUES ('hedy', 'Gizmo', 2, 14.99, '2026-10-04');
sqlite> INSERT INTO daily_sales (day, orders, revenue) SELECT ordered_on, count(*), round(sum(qty * price), 2) FROM orders WHERE true GROUP BY ordered_on ON CONFLICT(day) DO UPDATE SET orders = excluded.orders, revenue = excluded.revenue;
sqlite> SELECT * FROM daily_sales;
day orders revenue
---------- ------ -------
2026-10-01 2 39.97
2026-10-02 2 94.92
2025-12-24 1 8.99
2026-10-03 1 9.99
2026-10-04 1 29.98ON CONFLICT needs a WHERE clause on the SELECT
Without a WHERE, SQLite can’t tell whether ON starts a join or the upsert, so the statement fails. The docs say to add WHERE true (INSERT):
sqlite> INSERT INTO customers (name) SELECT customer FROM orders ON CONFLICT(name) DO NOTHING;
Error: in prepare, near "DO": syntax error
me) SELECT customer FROM orders ON CONFLICT(name) DO NOTHING;
error here ---^
sqlite> INSERT INTO customers (name) SELECT customer FROM orders WHERE true ON CONFLICT(name) DO NOTHING;The second statement, with WHERE true, runs. See SQLite upsert for more on ON CONFLICT.
Conclusion
Use INSERT INTO … SELECT when the rows you need already exist in another table or query. Check the SELECT on its own first, then add the INSERT. Use CREATE TABLE … AS SELECT when the target table doesn’t exist yet. When the target already has some of the rows, pick WHERE NOT EXISTS to skip them or ON CONFLICT to update them.
Further reading
- SQLite INSERT
- SQLite SELECT
- SQLite CREATE TABLE
- SQLite upsert
- SQLite ATTACH DATABASE
- SQLite docs: INSERT
Tested with SQLite 3.51.0 on 7 October 2026.