# SQL INSERT INTO SELECT: Copy Query Results Into a Table

- URL: https://betteranalyst.com/sql/insert-into-select

SQL guide · Changing data

`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

- [SQL INSERT INTO](https://betteranalyst.com/sql/insert-into)
- [SQL SELECT INTO](https://betteranalyst.com/sql/select-into)
- [SQL SELECT](https://betteranalyst.com/sql/select)
- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL UPDATE](https://betteranalyst.com/sql/update)
- [SQL CREATE TABLE](https://betteranalyst.com/sql/create-table)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)