iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

SQL INSERT INTO SELECT

By · Reviewed & last updated · Part of SQL Tutorial

INSERT INTO … SELECT copies rows from one table into another that already exists.

Example

SQL
INSERT INTO customers_archive (name, email)
SELECT name, email
FROM customers
WHERE active = 0;

Selecting all columns

SQL
INSERT INTO customers_archive
SELECT *
FROM customers
WHERE deleted_at IS NOT NULL;

The column counts and types of source and destination must line up.

Mapping with literals and expressions

SQL
INSERT INTO audit_log (table_name, action, row_id, occurred_at)
SELECT 'customers', 'archived', id, NOW()
FROM customers
WHERE active = 0;

vs SELECT INTO

StatementUse when
INSERT INTO existing SELECT …The destination table already exists.
SELECT … INTO new (SQL Server / Postgres)You want the statement to create the destination.
CREATE TABLE new AS SELECT …Portable equivalent of SELECT INTO.
Tip: Wrap large INSERT-SELECTs in a transaction. If something fails halfway, you can ROLLBACK and leave the destination clean.

Example

Example
INSERT INTO customers_archive (name, email)
SELECT name, email FROM customers
WHERE active = 0;
Try it Yourself »

Exercise

Copy archived customers into an existing archive table.

INSERT INTO customers_archive (name) name FROM customers WHERE active = 0;

Test yourself

Q1. INSERT INTO … SELECT requires the destination to…
Q2. You can transform data on the way in by…
Q3. Wrapping a large INSERT-SELECT in a transaction lets you…

Discussion

Loading…