How To Insert Into Select Statement

The SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement is uesd to perform copy operation a data from one table and inserting it into another table.

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

Syntax: INSERT INTO SELECT

  
INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Example:

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

Let's Assume we are havinng two table STUDENT and COURSE.

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

The above SQL statement is used for being copy the details of the those students who are from “Andhra” from table “COURSE” and inserts into the table “STUDENT”

STUDENT table

STUDENT_ID    NAME     AGE     ADDRESS
----------    ------   ----    --------
101           Prayag   21       Ranchi
102           Rakesh   22       Delhi
103           Rahul    24       Punjab
104           Pankaj   25       Chennai

COURSE Table

COURSE_ID    STUDENT _ID     NAME     AGE     ADDRESS
----------   -----------     ------   ----    --------
1011           106           Prem     25       Chennai
1011           108           Tom      26       USA
1022           109           Trisha   27       Kolkata
1023           113           Hazra    22       Andhra

Result: STUDENT table

STUDENT_ID    NAME     AGE     ADDRESS
----------    ------   ----    --------
101           Prayag   21       Ranchi
102           Rakesh   22       Delhi
103           Rahul    24       Punjab
104           Pankaj   25       Chennai
113           Hazra    22       Andhra

COURSE Table

The COURSE Table would not be affected because we are copying content from this table and not making any changes in it therefore, It remains the same as above.

COURSE_ID    STUDENT _ID     NAME     AGE     ADDRESS
----------   -----------     ------   ----    --------
1011           106           Prem     25       Chennai
1011           108           Tom      26       USA
1022           109           Trisha   27       Kolkata
1023           113           Hazra    22       Andhra