SQL Insert Into Select

Filed Under: SQL

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“.

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

TeacherId TeacherName State Country
1 Amit Bengaluru India
2 Harry Texas US
3 John London UK

Student

StudentId StudentName State Country
1 Henry Wales UK
2 Rohit Delhi India
3 Steve London UK

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

SQL Insert Into Select

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages