Skip to content
Tutorials Center
My learning

Topics

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.

SQL
INSERT INTO archive (id, name)
SELECT id, name
FROM source
WHERE old = 1;

Try it

SQL Archiving old orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Loading a summary table ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
-- 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 EXISTS to 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 SELECT alone and check the rows before wrapping it in an INSERT.