SQL INSERT INTO SELECT

What is SQL INSERT INTO SELECT?

Copies rows returned by a SELECT query directly into another table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL INSERT INTO SELECT: Supported by PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, and most other relational databases.

SQL INSERT INTO SELECT Full Explanation

SQL INSERT INTO SELECT combines the power of INSERT and SELECT to move or copy data in bulk between tables or across databases. Instead of supplying literal VALUES, the statement takes the result set produced by a SELECT query and appends it to a target table. The column order or explicit column list must align between the target table and the SELECT output, both in number and compatible data types. Because the SELECT can include joins, aggregations, filters, or even subqueries, this construct is ideal for transforming data while loading it. Most engines treat the entire operation as a single transaction, rolling back on error unless autocommit is on. Advanced dialects add clauses like ON CONFLICT, IGNORE, or RETURNING, but the core behavior remains consistent: evaluate the SELECT, then insert its rows. Be mindful of constraints (primary keys, foreign keys, uniques) that may reject rows, and remember that triggers or default values on the target table still fire for each inserted row.

SQL INSERT INTO SELECT Syntax

INSERT INTO target_table [(column1, column2, ...)]
SELECT expression1, expression2, ...
FROM source_table
[WHERE conditions];

SQL INSERT INTO SELECT Parameters

target_table (identifier) - Table that will receive the new rows.
column_list (list) - Optional ordered list of columns in the target table that will be populated.
select_query (query) - Any valid SELECT statement whose result set matches the column list or table structure.

Example Queries Using SQL INSERT INTO SELECT

-- Copy all rows from staging_users to users
INSERT INTO users
SELECT * FROM staging_users;

-- Insert only recent orders for a single customer
INSERT INTO customer_orders (order_id, customer_id, total)
SELECT id, customer_id, amount
FROM orders
WHERE customer_id = 42
  AND order_date >= CURRENT_DATE - INTERVAL '30 days';

-- Transform data while loading into an aggregated fact table
INSERT INTO daily_sales (sale_date, total_revenue)
SELECT order_date::date, SUM(amount)
FROM orders
GROUP BY order_date::date;

Expected Output Using SQL INSERT INTO SELECT

Rows produced by the SELECT query are appended to the target_table
The engine returns the row-count inserted (for example, "INSERT 0 125" in PostgreSQL when 125 rows are written)

Use Cases with SQL INSERT INTO SELECT

Bulk migrate data from a staging table after ETL validation.
Backfill historical records into a fact table using transformations.
Create subsets of data (e.g., active users) in a reporting schema.
Archive old records from a high-traffic table into a long-term storage table.
Copy data across databases or servers via database links or federated tables.

Common Mistakes with SQL INSERT INTO SELECT

Mismatching number or order of columns between INSERT list and SELECT output.
Forgetting a WHERE clause and inserting more rows than intended.
Violating unique or foreign-key constraints, causing the entire statement to fail.
Assuming DEFAULT values will apply when the SELECT already returns a column.
Neglecting to lock or disable triggers when bulk loading in performance-sensitive situations.

First Introduced In

SQL-92 Standard

Frequently Asked Questions

What is the difference between INSERT VALUES and INSERT SELECT?

INSERT VALUES inserts explicit literal rows one at a time. INSERT SELECT pulls rows from a query, making it suitable for large or dynamic datasets.

Do I need to list the columns in the INSERT clause?

Only if the SELECT output columns are not in the same order or you want to omit certain target columns that have defaults. Providing an explicit list is safer.

How can I speed up large INSERT INTO SELECT operations?

Disable indexes or constraints temporarily, batch the inserts inside transactions, or use engine-specific bulk-loading features such as PostgreSQL's COPY or SQL Server's TABLOCK hint.

What happens if the SELECT returns zero rows?

The statement completes successfully and inserts nothing. The row count returned is 0.