Skip to content

Frequently Asked Questions - SQL Server INSERT Statement

How do I insert literal values or constants to a table or view?

To insert just literal values or constants into a table or view, you will use the simplest form of the INSERT statement, as follows:

INSERT INTO <table_name> | <view_name> [( <column_list> )]
VALUES ( <values_list> )

The VALUES keyword specified the values of one row of a table. The values are specified as a comma-separated list of scalar expressions whose data type, precision, and scale must be the same as or implicitly convertible to the corresponding column in the column list. If a column list is not specified, the values must be specified in the same sequence as the columns in the table or view.

To illustrate on how to use the INSERT INTO ... VALUES, given a Customers table:

CREATE TABLE [dbo].[Customers] (
[ID]        INT NOT NULL,
[FirstName] VARCHAR(50) NOT NULL,
[LastName]  VARCHAR(50) NOT NULL)

To insert a row in this table using literal values, you issue the following:

INSERT INTO [dbo].[Customers] ( [ID], [FirstName], [LastName] )
VALUES (1, 'Bill', 'Gates')

Since the column list is optional, you can also do the following. Just make sure that the sequence of the values being inserted matches the sequence of the columns in the table.

INSERT INTO [dbo].[Customers] VALUES (2, 'Larry', 'Ellison')

Although the column list is optional, it is recommended that it is always provided when doing an INSERT. This is to make sure that your INSERT statement will still work and each column will receive the correct value even if new columns are added or inserted between other columns in the table. If another column is added in the table and the same INSERT command is executed without the column list, the following error will be encountered:

Server: Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

How do I insert the rows of a table into another table?

To insert values into a table from one or more tables or views, you will use the SELECT subquery in the INSERT statement as follows:

INSERT INTO <table_name> | <view_name> [ ( <column_list> ) ]
SELECT <column_list> FROM <table_name> | <view_name>

Using a SELECT subquery lets more than one row be inserted at one time. The column list of the SELECT subquery must match the column list of the INSERT statement. If no column list is specified, the select list must match the columns in the table or view being inserted into. The SELECT subquery can reference one or more tables or views using joins.

To illustrate on how to use the INSERT statement with a SELECT subquery, assuming that you have 2 tables called [dbo].[Customers] and [dbo].[NewCustomers] and you want to insert into the [dbo].[Customers] table all records from the [dbo].[NewCustomers] table:

INSERT INTO [dbo].[Customers] ( [ID], [FirstName], [LastName] )
SELECT [ID], [FirstName], [LastName]
FROM [dbo].[NewCustomers]

Since the column list is optional, you can also do the following. Just make sure that the sequence of the values being inserted matches the sequence of the columns in the table.

INSERT INTO [dbo].[Customers] 
SELECT [ID], [FirstName], [LastName]
FROM [dbo].[NewCustomers]

Although the column list is optional, it is recommended that it is always provided when doing an INSERT. This is to make sure that your INSERT statement will still work and each column will receive the correct value even if new columns are added or inserted between other columns in the table. If another column is added in the table and the same INSERT command is executed without the column list, the following error will be encountered:

Server: Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

How do I insert into a table rows from another table and some literal values?

To insert values into a table from one or more tables or views and with some literal values for some columns, you will use the SELECT subquery in the INSERT statement as follows:

INSERT INTO <table_name> | <view_name> [ ( <column_list> ) ] SELECT <column_list>
| <literal_value> FROM <table_name> | <view_name>

Using a SELECT subquery lets more than one row be inserted at one time. The column list and literal values of the SELECT subquery must match the column list of the INSERT statement. The SELECT subquery can reference one or more tables or views using joins.

To illustrate on how to use the INSERT statement with a SELECT subquery, assuming that you have 2 tables called [dbo].[Customers] and [dbo].[NewCustomers] and you want to insert into the [dbo].[Customers] table all records from the [dbo].[NewCustomers] table and setting the [StatusFlag] to a value of 'New', the INSERT statement will look like the following:

INSERT INTO [dbo].[Customers] ( [ID], [FirstName], [LastName], [StatusFlag] )
SELECT [ID], [FirstName], [LastName], 'New'
FROM [dbo].[NewCustomers]