SQL Reference ยท Statements and Clauses
SQL INSERT INTO ... SELECT statement
Reference for SQL INSERT INTO ... SELECT: copy rows between tables, archive data, load summaries from a query, and avoid the common column-order mistakes.
INSERT INTO ... SELECT inserts the result of a query into a table. Instead of typing values, you let any SELECT produce the rows: copying data to an archive table, building a summary table, or loading a staging area into its final home.
The query can filter, join, aggregate and compute columns, so one statement can transform and move any amount of data. The query's columns are matched to the target columns by position, not by name.
INSERT INTO archive (id, name) SELECT id, name FROM source WHERE old = 1;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
INSERT INTO target_table (column1, column2, ...) SELECT expression1, expression2, ... FROM source_table [WHERE ...];
| Part | Description |
|---|---|
target_table (columns) |
Where the rows go and which columns they fill. |
SELECT ... |
Any query. Its select list must have the same number of columns as the column list, in the same order, with compatible types. |
Result: the number of rows inserted. If one row violates a constraint, the whole statement fails and nothing is inserted.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | Standard form. INSERT OR IGNORE INTO ... SELECT skips rows that collide with a unique key. WITH may precede the insert. When an upsert clause follows INSERT ... SELECT, give the query a WHERE clause (WHERE true is enough) to avoid a parsing ambiguity. |
| PostgreSQL | Standard form with RETURNING and ON CONFLICT. |
| MySQL | Standard form with ON DUPLICATE KEY UPDATE; INSERT IGNORE skips errors. |
| SQL Server | Standard form; OUTPUT inserted.* returns rows. SELECT ... INTO new_table creates and fills a new table in one step. |
| Oracle | Standard form; INSERT /*+ APPEND */ for bulk loads, and INSERT ALL for multi-table inserts. |
-- SQL Server: create and fill in one statement SELECT id, customer_id, ordered_at INTO cancelled_orders FROM orders WHERE status = 'cancelled';
Usage notes
- Match columns by position. Name the target columns so a changed table layout cannot silently shift values into the wrong column.
- Reading and writing the same table works in most databases (MySQL needs the source wrapped in a derived table), but the query sees a consistent snapshot.
- Skip generated keys: omit an auto-increment id from both lists and the database numbers the new rows.
- Duplicates: add an upsert clause or filter with
NOT EXISTSto avoid unique-key errors when loading repeatedly. - Large loads run faster inside a single transaction and with indexes on the target added afterwards.
- To create the target at the same time, see
CREATE TABLE AS. - Test first: run the
SELECTalone and check the rows before wrapping it in anINSERT.
Related
INSERT INTO, UPSERT,CREATE TABLE AS,SELECT- Lessons: INSERT, Import and export