SQL Insert Into Select With Examples [Latest]

SQL Insert Into Select

SQL Insert Into Select clause is used when we want to copy data from one table to another table.

Rules For SQL Insert Into Select

• INSERT INTO SELECT requires that data types in source and target tables match.
• The existing records in the target table are unaffected.

SQL Insert Into Select Syntax

INSERT INTO table2 (column1, column2, ... , columnN)
SELECT c1, c2, ... , cn FROM table1
WHERE condition;

In the above syntax, data from table1 is selected using SELECT statement and then is inserted into the table2 using INSERT statement.

SQL Insert Into Select Example

Let us consider the following table for understanding SQL Insert Into Select Statement.

Teacher

Student

Query for the tables:

CREATE TABLE `teacher` (
  `TeacherId` INT NOT NULL,
  `TeacherName` VARCHAR(45) NULL,
  `State` VARCHAR(45) NULL,
  `Country` VARCHAR(45) NULL,
  PRIMARY KEY (`TeacherId`),
  UNIQUE INDEX `TeacherId_UNIQUE` (`TeacherId` ASC) VISIBLE);
CREATE TABLE `student` (
  `StudentId` INT NOT NULL,
  `StudentName` VARCHAR(45) NULL,
  `State` VARCHAR(45) NULL,
  `Country` VARCHAR(45) NULL,
  PRIMARY KEY (`StudentId`),
  UNIQUE INDEX `StudentId_UNIQUE` (`StudentId` ASC) VISIBLE);
Insert into Teacher(TeacherId,TeacherName,State,Country) VALUES (1, 'Amit','Bengaluru','India'), (2, 'Harry','Texas','US'), (3, 'John','London','UK');
Insert into Student(StudentId,StudentName,State,Country) VALUES (1, 'Henry','Wales','UK'), (2, 'Rohit','Delhi','India'), (3, 'Steve','London','UK');

Let us assume a case when the Student from India got a teaching job in the same Institute. In that case, the data for students from India need to be copied to the data in Teacher table.

Insert into Teacher (TeacherId,TeacherName,State,Country)
Select 4,StudentName,State,Country from Student where country = 'India';

Notice that there is already a teacher with id 2, so we are using “select 4” to use a different id for the student data that we are copying to the teacher table.

Below image shows the teacher table data after the command execution.

Conclusion

SQL insert into select clause is very helpful in copying data from one table to another. We can use it to create a selective dump of a table data. It’s supported by all the major SQL database vendors such as MySQL, Oracle, SQL Server, PostgreSQL etc.