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.

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.

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.

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.

1 comment:

You left out a few important things. The only date format allowed in ANSI/ISO standard SQL is based on ISO 8601 (yyyy-mm-dd). The old Sybase "select… Union all" syntax is strictly dialect in the really bad programming practice. Rows are nothing like records, so you get the terminology wrong. None of your examples showed passing and expression in an insertion statement. You did not show how to use the create sequence statement in an insertion. Most of your tables were not really tables at all! By definition, a table has to have a key.