SQL INSERT INTO SELECT

SQL INSERT INTO SELECT statement selects data from one table and insert it into another table.

Basically, it copies data from table you can called as source table to another table that is target table. Columns data types in source and target tables must match.

It does not require that target table must have same number of columns as defined in source table.

SYNTAX

INSERT INTO Target_Table 
SELECT Col1, Col2,..
FROM Source_Table ;

Lets look at an example of INSERT INTO SELECT statement in SQL Server.

To demonistrate this, first we create a source table named as EMP_SOURCE and insert some records in source table .

Then will create a target table named as EMP_TARGET.

CREATE TABLE EMP_SOURCE (
EMPID INT IDENTITY(1,1), 
EMPNAME VARCHAR(50),
EMPCODE VARCHAR(10)
)

INSERT INTO EMP_SOURCE (EMPNAME,EMPCODE)
VALUES (‘RAJ SINGH’,‘XRT78’) ,
(‘SUNIL RAWAT’,‘VFT79’),
(‘MANISH KR‘,‘YUI98’),
(‘AJAY AGGARWAL’,‘ERT65’)

CREATE TABLE EMP_TARGET (
ROWID INT IDENTITY(1,1),
EMPID INT , 
EMPNAME VARCHAR(50),
EMPCODE VARCHAR(10)
)

As you can see, there are some records in EMP_SOURCE table that we have inserted while EMP_TARGET table is blank.

SELECT * FROM EMP_SOURCE
SELECT * FROM EMP_TARGET

 

Now, we will copy the EMP_SOURCE table data into EMP_TARGET table.

Following SQL statement copies the data from source table that is EMP_SOURCE into Target table that is EMP_TARGET.

INSERT INTO EMP_TARGET(EMPID, EMPNAME, 	EMPCODE )
SELECT EMPID, EMPNAME, EMPCODE 
FROM EMP_SOURCE ;

 

 

Now you can check the taget table, and you will see that all records from source table are copied into target table.

SELECT * FROM EMP_SOURCE
SELECT * FROM EMP_TARGET

Copy only specific column values

In case, if you do not want to copy values for all columns in a table then you can also copy data for specific columns into target table.

Lets say you want to copy a value for EmpName column only for EMPID =1 then in this case you will have to modify above statement by selecting only EMPName column in select statement also need to add where condition as given below.

INSERT INTO EMP_TARGET(EMPNAME)
SELECT EMPNAME 
FROM EMP_SOURCE WHERE EMPID =1 ;

SELECT * FROM EMP_SOURCE
SELECT * FROM EMP_TARGET

You can see,  one row is copied into target table for EMPID =1 , and only EMPNAME column has value.

This time EMPID and EMPCODE columns are having null values as value for these column have not provided in select statement.

Also Read..

SELECT INTO SELECT