SQL guide · Changing data

SQL INSERT INTO SELECT: Copy Query Results Into a Table

INSERT INTO ... SELECT adds rows to an existing table using the result of a query instead of a fixed VALUES list. The query can read from one or several tables, filter, join and aggregate, and every row it returns is inserted into the target table. It is the standard way to copy data, archive old records, or load a summary table from detail rows.

The target table must already exist, which distinguishes this statement from SELECT INTO or CREATE TABLE AS, both of which create a new table. The columns returned by the SELECT are matched to the target columns by position, so the number of columns and their data types need to be compatible. Naming the target columns explicitly is the safest way to keep the two in line.

Because the whole statement runs as one operation, it is much faster than fetching rows into an application and inserting them one at a time. It is also atomic in most databases: if a single row violates a constraint, the entire insert is rolled back and nothing is added.

Basic syntax

INSERT INTO target_table (column1, column2, column3)
SELECT source_col1, source_col2, source_col3
FROM source_table
[WHERE condition];

The column list in parentheses names the target columns, and the SELECT list must supply the same number of values in the same order. Any target column you leave out receives its default or NULL. The SELECT can be as complex as needed, including joins, GROUP BY and subqueries.

SQL INSERT INTO SELECT examples

Copy rows to an archive table

This copies orders older than 2024 into an archive table with the same structure. Running a separate DELETE afterwards completes a move, ideally inside one transaction.

INSERT INTO orders_archive (order_id, customer_id, order_date, status)
SELECT order_id, customer_id, order_date, status
FROM orders
WHERE order_date < '2024-01-01';

Load a summary table

An aggregate query can feed the insert. Here monthly revenue is computed from the detail tables and stored in a reporting table that dashboards can read quickly.

INSERT INTO monthly_revenue (revenue_month, revenue)
SELECT DATE_TRUNC('month', o.order_date),
       SUM(oi.quantity * oi.unit_price)
FROM orders AS o
INNER JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY DATE_TRUNC('month', o.order_date);

Insert only rows that do not exist yet

A NOT EXISTS test prevents duplicates. Only customers missing from the mailing list are added, so the statement can be run repeatedly without creating copies.

INSERT INTO mailing_list (customer_id, email)
SELECT c.customer_id, c.email
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM mailing_list AS m
  WHERE m.customer_id = c.customer_id
);

Mix computed and constant values

The select list may contain literals and expressions. This stamps each copied row with a fixed source label and today's date while pulling the rest from the staging table.

INSERT INTO customers (customer_name, email, source, imported_on)
SELECT full_name, email_address, 'csv_import', CURRENT_DATE
FROM staging_customers;

Where SQL dialects differ

PostgreSQL: Supports the standard form plus RETURNING, and ON CONFLICT clauses for handling duplicates. CREATE TABLE ... AS SELECT creates and fills a new table in one step.

MySQL: Supports the standard form with ON DUPLICATE KEY UPDATE and INSERT IGNORE. Selecting from the same table you insert into is allowed, because MySQL buffers the rows in a temporary table first.

SQL Server: Supports the standard form, and SELECT ... INTO new_table creates a new table from a query. Use OUTPUT to return inserted rows, and TABLOCK hints can enable minimally logged bulk loads.

SQLite: Supports INSERT INTO ... SELECT and INSERT OR IGNORE ... SELECT. When combining it with an upsert ON CONFLICT clause, add WHERE true to the select to avoid a parsing ambiguity.

Oracle: Supports the standard form, the /*+ APPEND */ hint for direct-path loads, and INSERT ALL for loading several tables from one query.

Common mistakes

  • Relying on column position without a column list. If the source or target table changes, values silently land in the wrong columns.
  • Mismatched column counts or incompatible types, such as inserting text into a numeric column. The statement fails and no rows are inserted.
  • Forgetting that the target table must exist. Use CREATE TABLE AS or SELECT INTO to make a new table, depending on your database.
  • Running it twice and duplicating data. Add a NOT EXISTS check or a unique constraint so repeated runs are safe.
  • Moving data with insert and delete as two separate transactions. A failure in between can lose or duplicate rows, so wrap both in one transaction.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator