T-SQL Insert Into Table Examples

In T-SQL, to add records into a table (SQL Server) we use a Data Manipulation Language (DML) command known as Insert statement. In this article, we are going to explore the different capabilities of the T-SQL Insert statement.

Let us create a table Table1 with 3 columns A, B and C

create table Table1
(A int,
 B Varchar(10),
 C Date)

Example 1: Add one complete row, date in American format

Here we will mention the actual column values in the Values clause in a sequence that matches the columns mentioned in the CREATE TABLE statement.

Syntax: Insert into Table_Name Values(Col1 Value, Col2 Value….., Coln Value)

Insert into Table1 Values (1,'Aa', '12/13/2014')

Example 2: Add one complete row, date in British format

The date can be entered either in American or British format.

Insert into Table1 Values (2,'Bb', '13-Dec-2014')

Example 3: Add one partial row, specifying Null
Sometimes we do not want to add values in every column. Let us create a new row by specifying values for A and C columns only.

Insert into Table1 Values (3, Null, '14-Dec-2014') 

Example 4: Add one partial row, specifying column names

Consider there are 25 columns in a SQL table, and we want to create a new row by specifying values for only 3 columns. In that case, mentioning Null for the remaining 22 columns multiple times will be cumbersome. So instead of that, we can only mention those 3 columns after the table name, and mention the values for only those 3 columns. The remaining 22 columns by default will get null values. Caution: There should not be primary key or NOT NULL constraint to any of those columns.

Syntax: Insert into Table_Name (Column1, Column3, Column5) Values(Col1 Value, Col3 Value, Col5 Value)

Insert into Table1(A,C) Values (4,'15-Dec-2014')

Note: The columns can be mentioned in any sequence after the table name, but then the values need to mentioned in the same sequence.

Example 5: Ignore the identity column while adding row.

If the table has identity (auto number generation property) column, then by default we cannot add value to that column.

Let us create table Table2 with A as identity column and B, C as integer data type columns

create table Table2
(A int identity,
 B int,
 C int)

To add a record, use the syntax demoed in Example 4.

Insert into Table2 (B,C) Values(100,200)

Example 6: Ignore the computed column while adding row

Let us create table Table3 with a,b and c as 3 columns. C will be the computed column.

create table Table3
(A int,
 B int,
 C as (a + b))

Insert into Table3 Values(200, 300)

The column C will implicitly get value 500!

Example 7: Bulk Insertion – Select along with Union All

If we want to add many records in a table through one statement, then SELECT statement can be associated with INSERT statement instead of values clause.

Let us create table Table4 first.

create table Table4
(a int, b varchar(10)
)

Now if we want add five rows in Table4, we will use SELECT statement along with UNION ALL.

Insert into Table4
Select 1,'A'
Union All
Select 2,'B'
Union All
Select 3,'C'
Union All
Select 4,'D'
Union All
Select 5,'E'

Example 8: Bulk Insertion – Select along with Union All, violation of constraint

If any record violates the constraint, then the entire set gets rejected.

create table Table5
(a int check (a <= 3), b varchar(10)
)

Insert into Table5
Select 1,'A'
Union All
Select 2,'B'
Union All
Select 3,'C'
Union All
Select 4,'D'
Union All
Select 5,'E'

No rows will get added in Table 5.

Example 9: Bulk Insertion – Multiple rows through Values clause itself

From SQL Server 2008 version onwards, the Values clause has been strengthen to add multiple rows.

Syntax:

Insert Into TableName
Values    (……………..),
           (……………..),
           (……………..),
           (……………..)

So in the Table4 now, we will add three records using the Values clause.

Insert into Table4
Values (6,'E'),(7,'F'),(8,'G')

Note: If any one record fails then the entire batch gets rejected.

Example 10: Bulk Insertion – Copying records of another table

Many a times we need to copy records from one table to another.

Insert into Target_Table
Select * from Source_Table

Note: Here it is assumed that the structure of source & target tables are same. That means the number of columns should be same, and positional columns should be compatible as per data type & size.
Let us create table Table6 similar to Table4

create table Table6
(a int, b varchar(10)
)

Now all records of Table4 need to be inserted in Table6.

Insert into Table6
Select * from Table4

Example 11: Bulk Insertion – Copying records of another table, but not in all columns.

Let us create table Table7 similar to Table4

create table Table7
(a int, b varchar(10)
)

Here we need to mention the column names. Suppose we want to add records, but taking values of column B only, then the command will be:

Insert into Table7(B)
Select B from Table4

Conclusion:

In this article we have seen how to add records into an SQL Server table using the T-SQL Insert statement with its multiple examples.