INSERT INTO SELECTDML
Buy Me A Coffee

Copy rows from one query result into a table, optionally transforming them in transit. The fundamental pattern for populating summary tables, migrating data, and archiving rows.

Inserts rows computed from a SELECT query, the standard way to copy data between tables, transform data during insertion, and populate tables from computed results.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 3.23 INSERT INTO ... SELECT is fully supported. Column list in the INSERT is optional if the SELECT returns columns in the exact same order and count as the table. CREATE TABLE ... SELECT creates and populates a new table in one statement (MySQL-specific shorthand). AUTO_INCREMENT columns are populated normally during INSERT INTO ... SELECT.
PostgreSQL ✓ Supported 6.0 INSERT INTO ... SELECT is fully supported. SELECT INTO creates a new table from a query (PostgreSQL-specific; standard SQL uses CREATE TABLE AS SELECT). CREATE TABLE AS SELECT is the portable form. Can include ON CONFLICT clause for upsert behavior on the destination table.
SQL Server ✓ Supported 6.5 INSERT INTO ... SELECT fully supported. SELECT INTO creates a new table (SQL Server extension, same as PostgreSQL). For large inserts, TABLOCK hint with minimal logging (simple recovery model) dramatically reduces log growth. The OUTPUT clause captures inserted rows in the same statement. Can use OPENROWSET or linked servers as the source.
Oracle ✓ Supported 7 INSERT INTO ... SELECT fully supported. CREATE TABLE AS SELECT (CTAS) is the Oracle form for create-and-populate. INSERT ALL allows inserting into multiple tables from a single SELECT. The APPEND hint enables direct-path insert (faster, bypasses buffer cache, locks the table for the duration).
SQLite ✓ Supported 1.0 INSERT INTO ... SELECT fully supported. CREATE TABLE AS SELECT creates and populates a new table. Wrapping a large INSERT INTO ... SELECT in a transaction is critical for performance — SQLite without a transaction performs an fsync per row.

Details

INSERT INTO ... SELECT is universally supported and has been since the earliest versions of all five engines. The more interesting divergences are in the create-and-populate shorthand: CREATE TABLE AS SELECT is the SQL standard form and works in MySQL, PostgreSQL, Oracle, and SQLite; SELECT INTO is a SQL Server and PostgreSQL extension. Oracle's INSERT /*+ APPEND */ hint enables direct-path loading (equivalent to bulk load, bypasses buffer cache) but exclusively locks the segment until COMMIT. Oracle's INSERT ALL is unique — it can fan one SELECT into multiple target tables in a single statement, useful for partitioned loading.

Standard Syntax

-- Insert rows from another table: INSERT INTO archive_orders (id, customer_id, amount, archived_at) SELECT id, customer_id, amount, NOW() FROM orders WHERE order_date < '2023-01-01'; -- Create and populate a new table in one step: SELECT id, customer_id, amount INTO new_table FROM orders; -- SQL Server syntax

Version Support

MySQL: Since 3.23 PostgreSQL: Since 6.0 SQL Server: Since 6.5 Oracle: Since 7 SQLite: Since 1.0

Per-Database Syntax & Notes

MySQL

INSERT INTO ... SELECT is fully supported. Column list in the INSERT is optional if the SELECT returns columns in the exact same order and count as the table. CREATE TABLE ... SELECT creates and populates a new table in one statement (MySQL-specific shorthand). AUTO_INCREMENT columns are populated normally during INSERT INTO ... SELECT.

-- Standard INSERT INTO ... SELECT: INSERT INTO archive_orders (id, customer_id, amount, archived_at) SELECT id, customer_id, amount, NOW() FROM orders WHERE order_date < '2023-01-01'; -- Create and populate (MySQL-specific): CREATE TABLE orders_backup AS SELECT * FROM orders; -- With transformation: INSERT INTO product_summary (category, total_revenue, avg_price) SELECT category, SUM(price * quantity), AVG(price) FROM order_items GROUP BY category; -- Ignore duplicate key conflicts: INSERT IGNORE INTO users_cache SELECT id, email FROM users_import;

PostgreSQL

INSERT INTO ... SELECT is fully supported. SELECT INTO creates a new table from a query (PostgreSQL-specific; standard SQL uses CREATE TABLE AS SELECT). CREATE TABLE AS SELECT is the portable form. Can include ON CONFLICT clause for upsert behavior on the destination table.

-- Standard INSERT INTO ... SELECT: INSERT INTO archive_orders (id, customer_id, amount, archived_at) SELECT id, customer_id, amount, NOW() FROM orders WHERE order_date < '2023-01-01'; -- Create and populate: CREATE TABLE orders_backup AS SELECT * FROM orders; -- PostgreSQL-specific SELECT INTO (avoid in stored procedures): SELECT * INTO orders_backup FROM orders; -- With ON CONFLICT: INSERT INTO product_cache (id, price) SELECT id, price FROM products ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price; -- From a CTE: WITH recent AS ( SELECT * FROM orders WHERE order_date >= CURRENT_DATE - 30 ) INSERT INTO recent_orders_summary SELECT customer_id, COUNT(*), SUM(amount) FROM recent GROUP BY customer_id;

SQL Server

INSERT INTO ... SELECT fully supported. SELECT INTO creates a new table (SQL Server extension, same as PostgreSQL). For large inserts, TABLOCK hint with minimal logging (simple recovery model) dramatically reduces log growth. The OUTPUT clause captures inserted rows in the same statement. Can use OPENROWSET or linked servers as the source.

-- Standard INSERT INTO ... SELECT: INSERT INTO archive_orders (id, customer_id, amount, archived_at) SELECT id, customer_id, amount, GETDATE() FROM orders WHERE order_date < '2023-01-01'; -- Create and populate (SQL Server extension): SELECT * INTO orders_backup FROM orders; -- Or standard form: SELECT * INTO orders_backup FROM orders WHERE 1=0; -- structure only -- With OUTPUT to capture inserted IDs: INSERT INTO product_cache (id, price) OUTPUT INSERTED.id, INSERTED.price INTO @inserted_log SELECT id, price FROM products; -- Bulk hint for minimal logging: INSERT INTO staging_table WITH (TABLOCK) SELECT * FROM source_table;

Oracle

INSERT INTO ... SELECT fully supported. CREATE TABLE AS SELECT (CTAS) is the Oracle form for create-and-populate. INSERT ALL allows inserting into multiple tables from a single SELECT. The APPEND hint enables direct-path insert (faster, bypasses buffer cache, locks the table for the duration).

-- Standard INSERT INTO ... SELECT: INSERT INTO archive_orders (id, customer_id, amount, archived_at) SELECT id, customer_id, amount, SYSDATE FROM orders WHERE order_date < DATE '2023-01-01'; -- Create and populate (CTAS): CREATE TABLE orders_backup AS SELECT * FROM orders; -- Direct-path insert (APPEND hint — faster for large volumes): INSERT /*+ APPEND */ INTO archive_orders SELECT * FROM orders WHERE order_date < DATE '2023-01-01'; COMMIT; -- APPEND requires explicit commit before the table is accessible -- INSERT ALL into multiple tables: INSERT ALL INTO us_orders SELECT * WHERE region = 'US' INTO eu_orders SELECT * WHERE region = 'EU' INTO other_orders SELECT * WHERE region NOT IN ('US','EU') SELECT * FROM orders;

SQLite

INSERT INTO ... SELECT fully supported. CREATE TABLE AS SELECT creates and populates a new table. Wrapping a large INSERT INTO ... SELECT in a transaction is critical for performance — SQLite without a transaction performs an fsync per row.

-- Standard INSERT INTO ... SELECT: INSERT INTO archive_orders (id, customer_id, amount) SELECT id, customer_id, amount FROM orders WHERE order_date < '2023-01-01'; -- Create and populate: CREATE TABLE orders_backup AS SELECT * FROM orders; -- Wrap in transaction for performance: BEGIN; INSERT INTO archive_orders SELECT * FROM orders WHERE order_date < '2023-01-01'; COMMIT; -- With INSERT OR IGNORE for conflict handling: INSERT OR IGNORE INTO users_cache SELECT id, email FROM users_import;