SQL Insert Into Select

The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table. Unlike SELECT INTO (which creates a new table on the fly), INSERT INTO SELECT requires the target table to already exist in the database.

This statement is widely used for data archiving, ETL (Extract, Transform, Load) pipelines, combining datasets, and populating reporting tables.

Basic Syntax

The data types of the source columns must match or be implicitly convertible to the data types of the corresponding target columns.

Copying Specific Columns (Recommended)

INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;

Copying All Columns

If you are inserting data into all columns in the exact structural order of the target table, you can omit the target column list:

INSERT INTO target_table
SELECT *
FROM source_table
WHERE condition;

Best Practice: Always explicitly list column names. Relying on SELECT * can cause query failures or mismatched data if table schemas are modified later.

How It Works

 SOURCE TABLE (e.g., NewLeads)          TARGET TABLE (e.g., Customers)
+----+---------------+---------+      +----+---------------+---------+
| ID | Name          | City    |      | ID | Name          | City    |
+----+---------------+---------+      +----+---------------+---------+
| 10 | Acme Corp     | London  |      | 1  | Global Tech   | NYC     |
| 11 | Apex Ltd      | Berlin  | ---> | 2  | Apex Ltd      | Berlin  | (Inserted)
+----+---------------+---------+      +----+---------------+---------+
            |
            v
     WHERE City = 'Berlin'

Practical Examples

1. Copying Filtered Rows into an Existing Table

Suppose you have a target table named Suppliers and a source table named Customers. To append all customers from 'Norway' into the Suppliers table:

INSERT INTO Suppliers (SupplierName, City, Country)
SELECT CustomerName, City, Country
FROM Customers
WHERE Country = 'Norway';

2. Inserting Static Values Alongside Selected Data

You can combine columns from a source table with hardcoded literal values or expressions in the SELECT list.

Example: Archive completed orders into an OrderArchive table while recording who performed the operation and the timestamp:

INSERT INTO OrderArchive (OrderID, CustomerID, OrderDate, TotalAmount, ArchivedBy, ArchivedDate)
SELECT 
    OrderID, 
    CustomerID, 
    OrderDate, 
    TotalAmount, 
    'SystemAdmin' AS ArchivedBy, 
    CURRENT_TIMESTAMP AS ArchivedDate
FROM Orders
WHERE Status = 'Completed' AND OrderDate < '2026-01-01';

3. Inserting Aggregated Data

You can use GROUP BY and aggregate functions inside the SELECT statement to populate summary tables.

INSERT INTO DailySalesSummary (SalesDate, TotalOrders, TotalRevenue)
SELECT 
    CAST(OrderDate AS DATE) AS SalesDate,
    COUNT(OrderID) AS TotalOrders,
    SUM(TotalAmount) AS TotalRevenue
FROM Orders
WHERE OrderDate >= '2026-09-01' AND OrderDate < '2026-09-02'
GROUP BY CAST(OrderDate AS DATE);

4. Preventing Duplicate Inserts Using NOT EXISTS

To prevent inserting duplicate records that already exist in the target table:

INSERT INTO Customers (CustomerID, CustomerName, Country)
SELECT c.CustomerID, c.CustomerName, c.Country
FROM NewLeads c
WHERE NOT EXISTS (
    SELECT 1 
    FROM Customers target 
    WHERE target.CustomerID = c.CustomerID
);

Key Differences: SELECT INTO vs INSERT INTO SELECT