SQL INSERT INTO SELECT Statement

In the previous tutorial, we learned the SQL INSERT INTO statement. In this guide, we will see a useful variation SQL INSERT INTO SELECT statement which is used to copy the records from one table and inserts it into another table. You can think of it like a combination of SQL SELECT and INSERT INTO statements, SELECT fetches the records and INSERT INTO inserts those records in the table.

INSERT INTO SELECT Syntax

To copy all columns from one table and insert them into another table, we do not need to specify the column names.

INSERT INTO table_name2
SELECT * FROM table_name1
WHERE condition;

To copy only specific columns from one table and insert it into other table:

INSERT INTO table_name2 (column_name1, column_name2, ....)
SELECT column_name1, column_name2, ....
FROM table_name1
WHERE condition;

SQL INSERT INTO SELECT Example

Lets say we have two tables: STUDENT and COURSE

Table: STUDENT

STUDENT_ID    NAME     AGE     ADDRESS
----------    ------   ----    --------
101           Simon    26       Chennai
102           Daniel   27       Agra
103           Ramesh   26       Noida
104           Pappu    25       Noida

Table: COURSE

COURSE_ID    STUDENT _ID     NAME     AGE     ADDRESS
----------   -----------     ------   ----    --------
1011           106           Leon     25       Noida
1011           108           Tom      26       Agra
1022           109           Trisha   27       Noida
1023           113           David    25       Delhi

The following SQL statement copies the details of the those students who belong to “Delhi” from table “COURSE” and inserts into the table “STUDENT”

INSERT INTO STUDENT (STUDENT_ID, NAME, AGE, ADDRESS)
SELECT STUDENT_ID, NAME, AGE, ADDRESS FROM COURSE
WHERE ADDRESS='Delhi';

Result:
Table: STUDENT

STUDENT_ID    NAME     AGE     ADDRESS
----------    ------   ----    --------
101           Simon    26       Chennai
102           Daniel   27       Agra
103           Ramesh   26       Noida
104           Pappu    25       Noida
113           David    25       Delhi

Table: COURSE
This table is unaffected because we are copying content from this table and not making any changes in this table. It remains the same as above.