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

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:

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.

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.

By admin

Leave a Reply