SQL Server: INSERT Statement

In this post explains how to use the INSERT statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) INSERT statement is used to insert a single record or multiple records into a table in SQL Server.

Syntax

In its simplest form, the syntax for the INSERT statement when inserting a single record using the VALUES keyword in SQL Server (Transact-SQL) is:

INSERT INTO table
(column1, column2, ... )
VALUES
(expression1, expression2, ... ),
(expression1, expression2, ... ),
...;

However, the full syntax for the INSERT statement when inserting a single record using the VALUES keyword in SQL Server (Transact-SQL) is:

INSERT INTO table
(column1, column2, ... )
VALUES
( DEFAULT | NULL | expression1, 
  DEFAULT | NULL | expression2, 
  ...
);

Or...

The syntax for the SQL Server INSERT statement when inserting a single record using the DEFAULT VALUES keyword is:

INSERT INTO table
(column1, column2, ... )
DEFAULT VALUES;

Or...

In its simplest form, the syntax for the SQL Server INSERT statement when inserting multiple records using a sub-select is:

INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
[WHERE conditions];

However, the full syntax for the SQL Server INSERT statement when inserting multiple records using a sub-select is:

INSERT [ TOP (top_value) [ PERCENT ] ]
INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
[WHERE conditions];

Parameters or Arguments

table

The table to insert the records into.

column1, column2

The columns in the table to insert values.

expression1, expression2

The values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

TOP (top_value)

Optional. If specified, it will insert the top number of rows based on top_value. For example, TOP(10) would insert the top 10 rows from the full result set.

PERCENT

Optional. If PERCENT is specified, then the top rows are based on a top_value percentage of the total result set (as specfied by the PERCENT value). For example, TOP(10) PERCENT would insert the top 10% of the full result set.

source_table

The source table when inserting data from another table.

WHERE conditions

Optional. The conditions that must be met for the records to be inserted.

Note

• When inserting records into a table using the SQL Server INSERT statement, you must provide a value for every NOT NULL column.
• You can omit a column from the SQL Server INSERT statement if the column allows NULL values.

Example - Using VALUES keyword

The simplest way to create a SQL Server INSERT query to list the values using the VALUES keyword.

For example:

INSERT INTO employees
(employee_id, last_name, first_name)
VALUES
(10, 'Mark', 'Julie');

This SQL Server INSERT statement would result in one record being inserted into the employees table. This new record would have an employee_id of 10, a last_name of 'Mark', and a first_name of 'Julie'.

You can also use this syntax to insert more than one record at a time. For example:

INSERT INTO employees
(employee_id, last_name, first_name)
VALUES
(10, 'Mark', 'Julie'),
(11, 'Johnson', 'Dale');

This INSERT example shows how to insert more than one record using the VALUES keyword. In this example, two records are inserted into the employees table. The first record has an employee_id of 10, a last_name of 'Mark', and a first_name of 'Julie'. The second record has an employee_id of 11, a last_name of 'Johnson', and a first_name of 'Dale'.

This would be equivalent to the following two INSERT statements:

INSERT INTO employees
(employee_id, last_name, first_name)
VALUES
(10, 'Mark', 'Julie');

INSERT INTO employees
(employee_id, last_name, first_name)
VALUES
(11, 'Johnson', 'Dale');

Example - Using DEFAULT VALUES keyword

In SQL Server, you can also insert a record into a table using the DEFAULT VALUES syntax.

For example:

INSERT INTO employees
(employee_id, last_name, first_name)
DEFAULT VALUES;

This SQL Server INSERT statement would result in one record being inserted into the employees table. This new record would be created with default values for the employee_id, last_name, and first_name fields.

Example - Using SELECT statement

You can also create more complicated SQL Server INSERT statements using SELECT statements.

For example:

INSERT INTO contacts
(contact_id, last_name, first_name)
SELECT employee_id, last_name, first_name
FROM employees
WHERE employee_id = 100;

By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following SQL Server SELECT statement before performing the insert.

SELECT count(*)
FROM employees
WHERE employee_id = 100;