In the real world of SQL, imagine if there is a situation when you need to copy data from one table to another table. SQL has an answer for this and the answer is “SQL Insert Into Select“.
Table of Contents
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;
SQL Insert Into Select Example
Let us consider the following table for understanding SQL Insert Into Select Statement.
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.
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.