SQL - Insert Into Select

SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement is a powerful command that copies data from one table and inserts it into another table. This is extremely useful for tasks like archiving old data, creating summary tables, or populating staging tables in an ETL (Extract, Transform, Load) process.

The key requirement is that the data types in the source SELECT statement must be compatible with the data types of the columns in the target table.

Syntax

There are two main ways to use this statement:

1. Inserting All Columns

If you are inserting data into all columns of the target table, and the source table’s columns are in the same order and have compatible data types, you can use a simple SELECT *.

INSERT INTO target_table
SELECT * FROM source_table
WHERE condition;

2. Inserting Specific Columns

More commonly, you will specify which columns to insert into and which source columns to pull data from. This is more robust as it does not depend on the column order.

INSERT INTO target_table (column1, column2, column3)
SELECT source_column1, source_column2, source_column3
FROM source_table
WHERE condition;

Practical Application: Archiving Old Orders

Imagine you have an Orders table that is getting very large. You want to move all orders completed before 2022 into an Orders_Archive table to improve the performance of queries on the main Orders table.

-- Assume both 'Orders' and 'Orders_Archive' have the same structure:
-- (OrderID, CustomerID, OrderDate, TotalAmount, Status)

-- Step 1: Copy the old orders to the archive table
INSERT INTO Orders_Archive (OrderID, CustomerID, OrderDate, TotalAmount, Status)
SELECT OrderID, CustomerID, OrderDate, TotalAmount, Status
FROM Orders
WHERE OrderDate < '2022-01-01';

-- Step 2 (Caution!): After verifying the copy was successful, delete the old orders from the main table.
-- ALWAYS wrap this in a transaction.
BEGIN TRANSACTION;

DELETE FROM Orders
WHERE OrderDate < '2022-01-01';

-- If both steps succeed, commit.
COMMIT;

Copying a Limited Number of Rows

Sometimes you only need to copy a subset of rows, for example, for testing purposes. The syntax for limiting rows varies by SQL dialect.

Example: Copying the 10 Most Recent Orders

Standard SQL (PostgreSQL, Oracle):

INSERT INTO Sample_Orders
SELECT * FROM Orders
ORDER BY OrderDate DESC
FETCH FIRST 10 ROWS ONLY;

MySQL / MariaDB:

INSERT INTO Sample_Orders
SELECT * FROM Orders
ORDER BY OrderDate DESC
LIMIT 10;

SQL Server:

INSERT INTO Sample_Orders
SELECT TOP (10) * FROM Orders
ORDER BY OrderDate DESC;

Considerations and Best Practices

• Performance: Inserting a very large number of rows can be a heavy operation that locks tables and consumes significant transaction log space. Perform large data moves during low-traffic periods.
• Transactions: Always wrap INSERT INTO SELECT followed by a DELETE in a single transaction to ensure the operation is atomic. If the DELETE fails, the INSERT can be rolled back, preventing data duplication.
• Constraints and Triggers: Be aware that constraints (like NOT NULL, UNIQUE) and triggers on the target_table will be enforced during the insert. For very large bulk inserts, it might be faster to temporarily disable them, but this should be done with extreme care to not compromise data integrity.