SQL Server Error Messages - Msg 121¶

Error Message¶

Server: Msg 121, Level 15, State 1, Line 1
The select list for the INSERT statement contains more items than the insert list.
The number of SELECT values must match the number of INSERT columns.

Causes¶

As the error message describes, this error occurs when doing an INSERT to a table using the INSERT INTO ... SELECT FROM format and the number of columns specified in the SELECT clause is more than the number of columns specified in the INSERT clause.

To illustrate, the following INSERT statement will generate the error:

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName] )
SELECT [FirstName], [LastName], [Gender]
FROM [dbo].[Applicants]

Msg 121, Level 15, State 1, Line 1
The select list for the INSERT statement contains more items than the insert list.
The number of SELECT values must match the number of INSERT columns.

As can be seen from the INSERT INTO ... SELECT statement, there are 3 columns specified in the SELECT clause but only 2 columns are specified in the INSERT INTO clause.

This error is also usually encountered when in the SELECT clause instead of specifying the individual columns the "*" wild card is used and the number of columns in the source table is more than the number of columns in the destination table:

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [Gender] )
SELECT * FROM [dbo].[Applicants]

Msg 121, Level 15, State 1, Line 1
The select list for the INSERT statement contains more items than the insert list.

The number of SELECT values must match the number of INSERT columns.

Solution / Work Around:¶

To avoid this error, make sure that the number of columns specified in the SELECT clause matches the number of columns specified in the INSERT INTO clause:

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [Gender] )
SELECT [FirstName], [LastName], [Gender]
FROM [dbo].[Applicants]

If the destination table does not have enough columns to support the source table then only specify those columns in the SELECT clause that will have a correponding column in the destination table. In the example earlier, if the destination table [dbo].[Employees] does not have a [Gender] column then don't include that column in the SELECT clause:

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName] )
SELECT [FirstName], [LastName]
FROM [dbo].[Applicants]

Lastly, instead of using "*" in the SELECT clause, specify the individual columns that will match each column specified in the INSERT INTO clause:

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [Gender] )
SELECT [FirstName], [LastName], [Gender]
FROM [dbo].[Applicants]