SQL INSERT INTO SELECT

In this tutorial, we will learn how to use INSERT INTO SELECT statement using SQL.

SQL INSERT INTO SELECT Statement

SQL INSERT INTO SELECT statement is used to insert multiple records in a table of the database.
SQL INSERT INTO SELECT requires that data types of columns in the source table and target table match.

Syntax - Copy all columns from one table to another table

INSERT INTO tableName2
SELECT * FROM tableName1
WHERE CONDITIONs;

Note:- When you using this above Syntax make sure IDENTITY_INSERT is OFF in the table "TableName2". Otherwise, it gives an error, "An explicit value for the identity column in table 'tableName2' can only be specified when a column list is used and IDENTITY_INSERT is ON".

Syntax - Copy only some columns from one table into another table

INSERT INTO tableName2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM tableName1
WHERE condition;

Example:

Let us consider this table "Employee" for records.

Table Name: Employee

ID	EmpName	City	Country	Gender	Salary
1	Shankar	Delhi	India	male	25000
2	Sourabh	Delhi	India	male	30000
3	Ranvijay	Mumbai	India	male	15000
4	Kapil	Noida	India	male	25000
5	Shalini	Jaipur	India	female	18000
6	Rakesh	Faridabad	India	male	23000
7	Akshay	Mumbai	India	male	21000
8	Sarah	New York	US	female	76000
9	Rocky	Noida	India	male	28000

Example of INSERT INTO SELECT Statement

For inserting all the columns from Table1 to Table2 using INSERT INTO SELECT statement, a query will be:

Syntax - Insert all the Column using INSERT INTO SELECT statement

INSERT INTO BackupTable
SELECT * FROM Employee

Output

ID	EmpName	City	Country	Gender	Salary
1	Shankar	Delhi	India	male	25000
2	Sourabh	Delhi	India	male	30000
3	Ranvijay	Mumbai	India	male	15000
4	Kapil	Noida	India	male	25000
5	Shalini	Jaipur	India	female	18000
6	Rakesh	Faridabad	India	male	23000
7	Akshay	Mumbai	India	male	21000
8	Sarah	New York	US	female	76000
9	Rocky	Noida	India	male	28000

For inserting a specific column from Table1 to Table2 using INSERT INTO SELECT statement, a query will be:

Example - Inserting a specific column using INSERT INTO SELECT statement

INSERT INTO BackupTable (EmpName,City,Salary)
SELECT EmpName,City,Salary FROM Employee;

Output

ID	EmpName	City	Salary
1	Shankar	Delhi	25000
2	Sourabh	Delhi	30000
3	Ranvijay	Mumbai	15000
4	Kapil	Noida	25000
5	Shalini	Jaipur	18000
6	Rakesh	Faridabad	23000
7	Akshay	Mumbai	21000
8	Sarah	New York	76000
9	Rocky	Noida	28000