SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement in SQL is a very efficient way to copy data from one table to another. It's much faster than inserting rows one by one, especially when dealing with large datasets.

INSERT INTO SELECT: Definition and Usage

INSERT INTO SELECT copies data from the result set of a SELECT statement into a new table. The data types of the source and destination columns must match. Existing data in the target table is not affected; new rows are simply added. If you do not select all columns for the target table, the remaining columns in the new table will be filled with NULL values unless a default value is specified.

Syntax

-- Copy all columns:
INSERT INTO target_table SELECT * FROM source_table WHERE condition;

-- Copy specific columns:
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ... FROM source_table WHERE condition;
      

Examples

Copying Some Columns

This example copies 'SupplierName', 'City', and 'Country' from the 'Suppliers' table into the 'Customers' table. Any columns in 'Customers' that aren't specified will receive NULL values. (This assumes that 'Customers' and 'Suppliers' tables exist with compatible column structures.)

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
      

(Rows are added to the Customers table.  New CustomerIDs are generated.  Columns not specified in the INSERT statement will be NULL.)
      

Copying All Columns

This copies all columns from 'Suppliers' to 'Customers'.

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;
      

(All columns and rows from Suppliers are copied to Customers. New CustomerIDs are generated.)
      

Copying with a WHERE Clause

This copies only German suppliers to the 'Customers' table.

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

(Only German suppliers are copied to the Customers table. New CustomerIDs are generated.  Unspecificed columns will be NULL.)