INSERT INTO SELECT adds rows to an existing target table using the result of a query rather than a literal VALUES list.
Copying paid sales into a reporting table is a query-and-write operation. The SELECT chooses and transforms source rows; the INSERT maps those output expressions into target columns. The target definition and its constraints remain in control.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How INSERT INTO SELECT works
The destination table must already exist. Write its column list explicitly and ensure the SELECT returns the same number of expressions in the corresponding order. Column names do not automatically match across the two sides; position defines the mapping. INSERT INTO target (sale_id,total) SELECT id,amount FROM sales maps the first selected value to sale_id and the second to total regardless of their source aliases.
Preview the SELECT by itself before inserting. It reveals which records and transformed values will be written, but a preview and a later insert are not necessarily the same snapshot when other sessions can change source data. Use an appropriate transaction or a single insert statement when the workflow requires consistency. Check the number of rows affected and the relevant target keys after the write.
Constraints on the target table
Constraints can reject selected rows. A NOT NULL destination amount would reject sale 104 unless the SELECT excludes or correctly repairs its missing amount. A UNIQUE or primary-key rule can reject ids already present in the target. Do not disable those protections merely to make an import succeed. Decide how repeated runs should behave: fail on duplicates, insert only new records, or update existing records using the engine’s documented upsert mechanism.
INSERT INTO SELECT copies values, not the source table’s entire schema or identity configuration. Target defaults apply to omitted target columns according to ordinary insertion rules. Auto-generated keys should usually be omitted unless preserving source identities is intentional and supported by the dialect. Copying from and into related tables can also require attention to foreign-key order and transaction boundaries. If the SELECT joins several inputs, verify its row grain so one source sale is not inserted several times by accidental join multiplication.
MySQL, PostgreSQL, SQL Server and SQLite
The core INSERT INTO target(columns) SELECT ... form works in all four engines. Duplicate-conflict and upsert syntax differ: PostgreSQL and SQLite have ON CONFLICT forms, MySQL has ON DUPLICATE KEY UPDATE, and SQL Server uses other patterns. Identity insertion and returned generated keys also require local syntax.
Check the engine-specific syntax and version requirements in PostgreSQL INSERT.
Practice task and worked solution
Create a separate copy of the known pending sale amounts and verify it. Exclude missing amounts rather than replacing them with an invented value.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
CREATE TABLE pending_sales_copy (sale_id INTEGER PRIMARY KEY,total DECIMAL(10,2));
INSERT INTO pending_sales_copy (sale_id,total)
SELECT id,amount FROM sales WHERE status='pending' AND amount IS NOT NULL;
SELECT sale_id,total FROM pending_sales_copy ORDER BY sale_id;sale_id | total
102 | 30Two pending sales exist, but only sale 102 has a known amount. The source filter deliberately leaves sale 104 for data-quality follow-up. The explicit column mapping writes one record, and the readback verifies its identity and amount.
Run the paid-copy SELECT without its INSERT wrapper and count four source rows. After the copy, compare target count and total with that source selection. Try the same insert again in the disposable table and observe its duplicate-key rejection. Then inspect pending sale 104 to explain why an unknown amount requires an explicit policy rather than a silent coercion.
SQL INSERT INTO SELECT: Copy and Transform Query Results Syntax
INSERT INTO SELECT
CREATE TABLE paid_sales_copy (sale_id INTEGER PRIMARY KEY,total DECIMAL(10,2));
INSERT INTO paid_sales_copy (sale_id,total)
SELECT id,amount FROM sales WHERE status='paid';
SELECT sale_id,total FROM paid_sales_copy ORDER BY sale_id;SQL INSERT INTO SELECT: Copy and Transform Query Results Summary
INSERT INTO target (columns) SELECT ...writes the rows of a query into an existing table.- Columns are matched by position, so list the target columns explicitly and keep the same order in the
SELECT. - Target constraints still apply:
NOT NULL,UNIQUEand foreign keys can reject selected rows. - Run the
SELECTon its own first to check exactly what will be written. - Rerunning an import duplicates rows unless you add a policy such as
NOT EXISTSorON CONFLICT DO NOTHING.
SQL INSERT INTO SELECT: Copy and Transform Query Results Examples
Each example shows the code, the output it produces and what each part does.
Copying paid sales into a report table
The target is created explicitly. The SELECT supplies four paid records, and each id maps to sale_id while amount maps to total. The verification query reads the destination and confirms the copied values, including the anonymous paid sale.
CREATE TABLE paid_sales_copy (sale_id INTEGER PRIMARY KEY,total DECIMAL(10,2));
INSERT INTO paid_sales_copy (sale_id,total)
SELECT id,amount FROM sales WHERE status='paid';
SELECT sale_id,total FROM paid_sales_copy ORDER BY sale_id;sale_id | total
101 | 10
103 | 20
105 | 20
106 | 5Common SQL INSERT INTO SELECT: Copy and Transform Query Results Mistakes
Leaving out the target column list
Omitting the target column list makes the mapping depend on schema order. A later column addition can cause errors or wrong assignments. Re-running this example also hits its primary-key constraint because the same sale ids are already present; repeated imports need an explicit policy rather than assuming every run is additive.
SQL INSERT INTO SELECT: Copy and Transform Query Results in Real Projects
Loading approved rows in an ETL job
An ETL job can copy approved transactions into a typed reporting table while transforming field names and values. Keep rejection handling and rerun behaviour explicit. The reporting table should enforce the identity of each copied transaction so an accidental rerun cannot quietly double revenue.
Define rerun behaviour before scheduling a copy job. Preserving sale_id as a target key can catch repeated imports, but it does not by itself decide whether updates should be applied or ignored. Keep the SELECT preview, affected-row count and post-insert reconciliation tied to the same eligibility rule so an import’s apparent success is not mistaken for complete coverage.
SQL INSERT INTO SELECT: Copy and Transform Query Results FAQ
Does this statement create the target table?
No. INSERT INTO SELECT writes into an existing object. To create a new table from a query in one step, use CREATE TABLE new_table AS SELECT ..., or SELECT ... INTO new_table in SQL Server, as the SELECT INTO lesson explains.
Are source column names matched automatically?
No. The selected expressions map positionally to the named destination columns, so SELECT amount, id into (sale_id, total) puts amounts into sale_id. Aliases in the SELECT don't change this; only the order matters.
Is rerunning the import safe automatically?
No. Running it twice inserts the rows twice or fails on a primary key. Choose and implement a duplicate or idempotency policy, such as WHERE NOT EXISTS (SELECT 1 FROM target t WHERE t.sale_id = s.id) or ON CONFLICT DO NOTHING, using the engine's supported mechanisms.
Can I transform values while copying them?
Yes. The SELECT can calculate, rename and filter, for example SELECT id, ROUND(amount * 1.2, 2), UPPER(status) FROM sales WHERE status = 'paid'. Keep the original values somewhere if the transformation might need to be checked or repeated later.
Should a large copy run in one statement?
For moderate sizes, one statement in a transaction is simplest and all-or-nothing. For millions of rows, copying in batches, for example by id ranges, keeps transactions short, reduces lock time and lets a failed batch be retried without starting over.
SQL INSERT INTO SELECT: Copy and Transform Query Results Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
How many rows are written into pending_sales_copy in the worked solution?
SQL INSERT INTO SELECT: Copy and Transform Query Results Quiz
2 questions to check what you learned.
Must the target table already exist?
- No, INSERT INTO SELECT always creates its destination
- Only when a selected column contains NULL
- Yes, INSERT INTO SELECT writes into an existing table
How are source expressions mapped to target columns?
- By their order in the SELECT and destination column list
- By matching source aliases to destination names automatically
- By matching alphabetical column names regardless of expression order