SQL Server - INSERT INTO SELECT Keyword

The SQL Server (Transact-SQL) INSERT INTO SELECT keyword is used to populate a table using another table. Please note that the other table should have the set of fields which are required to populate in the first table.

Syntax

The syntax for using INSERT INTO SELECT keyword in SQL Server (Transact-SQL) is given below:

INSERT INTO table1 [(column1, column2, ...)] 
SELECT column1, column2, ...  FROM table2
[WHERE condition];

Example:

Consider a database containing tables called Employee and HREmployee with the following records:

Table 1: Employee table

Table 2: HREmployee table

The HREmployee table contains no records and have following fields - HRID, Name and Address.

• The given fields of HREmployee table can be populated using Employee table by using below query:

  INSERT INTO HREmployee (HRID, Name, Address)
  SELECT EmpID, Name, City FROM Employee
  WHERE EmpID IN (1, 4, 5);
  
  -- see the result
  SELECT * from HREmployee
  

  
  

  Now the HREmployee table will contain following records:

  HRID	Name	Address
  1	John	London
  4	Kim	Amsterdam
  5	Ramesh	New Delhi