SQL INSERT INTO SELECT

In this tutorial, we will help you to understand and learn how to copy records from one table and add them to another table in the SQL with the help of examples.

The INSERT INTO SELECT query is used to add records from one table to another using the SELECT statement. In other words, we can say this statement copies data from one table and adds it to another.

Before using the INSERT INTO SELECT query, we shouldn't forget the key points as follows:

1 The data we will add to the table must have existed in the database from the table we will copy.

2 The data types must be the same as the source and target table.

The syntax of the INSERT INTO SELECT statement is as follows:

INSERT INTO Table_Name_1 (SELECT * FROM Table_Name_2 WHERE condition);

In the above syntax, the where a condition is optional. The WHERE clause is used to add selected data from one table to another.

Use the below syntax to add records into selected columns using the INSERT INTO SELECT query is as follows:

INSERT INTO Table_Name_2 (Column_Name1, Column_Name2, Column_Name3, Column_Name4) SELECT Column_Name1, Column_Name2, Column_Name3, Column_Name4 FROM Table_Name_2 WHERE Condition;

Let’s understand how to use the INSERT INTO SELECT statement with the help of an examples.

First, we will create a new table name Student_information:

CREATE TABLE Student_Information (Studn_info_Id int not null, Student_Name varchar(40) not null, Student_gender varchar(1) not null, Student_age int not null, Marks int not null, Degree varchar(40) not null, Primary key(Studn_info_Id));

Let’s add some data into the Student_information table:

INSERT INTO Student_Information values(1, 'Priya Chaudhary', 'F', 23, 560, 'BE');
INSERT INTO Student_Information values(2, 'Utkarsh Kulkarni', 'M', 23, 550, 'B.Tech');
INSERT INTO Student_Information values(3, 'Rakhi Jain', 'F', 22, 580, 'M.COM');
INSERT INTO Student_Information values(4, 'Nikita Ingale', 'F', 23, 620, 'BE');
INSERT INTO Student_Information values(5, 'Piyush Narkhede', 'M', 22, 600, 'BSC');
INSERT INTO Student_Information values(6, 'Pawan Sharma', 'M', 24, 590, 'B.COM');
INSERT INTO Student_Information values(7, 'Tushar Mahalle', 'M', 22, 680, 'B.Tech');
INSERT INTO Student_Information values(8, 'Sakashi Sharma', 'F', 21, 650, 'BSC');
INSERT INTO Student_Information values(9, 'Gaurav Gupta', 'M', 22, 635, 'BSC');
INSERT INTO Student_Information values(10, 'Manish Kapoor', 'M', 23, 500, 'MCOM');

We will execute the select query to display the records in the table form as follows:

SELECT * FROM Student_Information;

Now, we will create one more table named Student_Info which is our target table in this example.

CREATE TABLE Student_Info (Stud_info_Id int not null, Student_Name varchar(40) not null, Student_gender varchar(1) not null, Student_age int not null, Marks int not null, Degree varchar(40) not null, Primary key(Stud_info_Id));

Now, we will have another table name Student_Infos, which will be our second target table in this example.

CREATE TABLE Student_Infos (Stud_info_Id int not null, Student_Name varchar(40) not null, Student_gender varchar(1) not null, Student_age int not null, Marks int not null, Degree varchar(40) not null, Primary key(Stud_info_Id));

Example 1: Write a query to add all the records from the student_information table to the Student_info table using the INSERT INTO SELECT query.

INSERT INTO Student_Info (SELECT * FROM Student_Information);

In the above INSERT INTO SELECT query example, we copied the entire student_information table data into the Student_Info table.

We will execute the SELECT query on the Student_Info table to verify whether the data is successfully added or not as follows:

SELECT * FROM Student_Info;

The output of the above query is as follows:

Example 2: Write a query to add all the records from the student_information table to the Student_infos table but only for selected columns using the INSERT INTO SELECT query.

INSERT INTO Student_Infos (Stud_Info_Id, Student_Name, Student_age, Marks, Degree) (SELECT Studn_Info_Id, Student_Name, Student_age, Marks, Degree FROM Student_Information); 

In the above INSERT INTO SELECT query example, we copied the selected column records from the student_information table data into the Student_Infos table.

We will execute the SELECT query on the Student_Info table to verify whether the data is successfully added or not as follows:

SELECT * FROM Student_Infos;

The output of the above query is as follows:

Now, we will have another table name Student_Infos, which will be our third target table in this example.

CREATE TABLE Students_Info (Stud_info_Id int not null, Student_Name varchar(40) not null, Student_gender varchar(1) not null, Student_age int not null, Marks int not null, Degree varchar(40) not null, Primary key(Stud_info_Id));

Example 3: Write a query to add the records from the student_information table to the Students_info table where student gender is ‘M’ using the INSERT INTO SELECT query.

INSERT INTO Students_Info (SELECT * FROM Student_Information WHERE Student_Gender = ‘M’);

In the above INSERT INTO SELECT query example, we copied the data from the student_information table where student gender is ‘M’ into the Students_Info table.

We will execute the SELECT query on the Students_Info table to verify whether the data is successfully added or not as follows:

SELECT * FROM Students_Info;

The output of the above query is as follows: