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

T-SQL - INSERT INTO



The T-SQL (Transact-SQL) INSERT INTO statement is used to insert a new record in a table. There are two ways of using INSERT INTO statement which are mentioned below.

Syntax

The below syntax specifies column names and respective values to be inserted.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

The below syntax specifies values only. Therefore, it is essential to specify values in the same order as the columns in the table.

INSERT INTO table_name 
VALUES (value1, value2, value3, ...);

Example:

Consider a database containing a table called Employee with the following records:

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

  • Using below query, two new records are added in the Employee table:

    INSERT INTO Employee (EmpID, Name, City, Age, Salary)
    VALUES (7, 'Suresh', 'Mumbai', 29, 2900);
    INSERT INTO Employee 
    VALUES (8, 'Zayne', 'Oslo', 31, 3400);
    
    -- see the result
    SELECT * from Employee
    

    Now the Employee table will contain following records:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    3JoParis272800
    4KimAmsterdam303100
    5RameshNew Delhi283000
    6HuangBeijing282800
    7SureshMumbai292900
    8ZayneOslo313400
  • Insert Data Only in Specified Columns: To insert data in specified columns, it is necessary to specify column names. See the query given below:

    INSERT INTO Employee (EmpID, Name, Age)
    VALUES (7, 'Suresh', 29);
    
    -- see the result
    SELECT * from Employee
    

    Now the Employee table will contain following records:

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

Populate one table using another table

A table can be populated using another table by using INSERT INTO SELECT statement. 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 statement 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