SQL Server Error Messages - Msg 120¶

Error Message¶

Server: Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer 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 less 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], [Gender] )
SELECT [FirstName], [LastName]
FROM [dbo].[Applicants]

Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer 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 INSERT INTO clause but only 2 columns are specified in the SELECT clause.

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 one of the columns to be populated in the destination table does not exist in one of the columns of the source table, there are a few of things that can be done to avoid this error. The first option is not to include the column in the destination table as part of the INSERT INTO clause. Assuming that the [Gender] column doesn't exist in the source table, the INSERT INTO ... SELECT statement will be as follows:

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

Another way of doing it is by including the destination column in the INSERT INTO clause and use NULL as the value in the SELECT clause, as can be seen in the following:

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

If the destination column does not allow NULLs, then an empty string can be used or one of the possible valid values for the column can be used. In the example above, if the [Gender] is not available from the source table, then a value of 'U' for 'Unknown' can be used:

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

Lastly, if the destination column does not allow NULLs but a default value is defined for the column, then DEFAULT can be used as the value in the SELECT clause:

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