;

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;
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