T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL - INSERT INTO SELECT Keyword



The T-SQL (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 T-SQL (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

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

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:

    HRIDNameAddress
    1JohnLondon
    4KimAmsterdam
    5RameshNew Delhi

❮ T-SQL Keywords