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

Feb 232011

I ran into the error: 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.
Well I’ve run into it often.

Normally this simply means you have specified fewer items in values for an INSERT statement than you have specified you need. For example:

INSERT INTO Table1
( ID, [Column], Column1 )
SELECT 1,
'Value',

Now, that’s easily rectified, by including the columns you need or removing the one from INSERT you do not need.

However, I also noticed that in my situation I had all the columns needed, but I was lacking a comma – like this:

INSERT INTO Table1
( ID, [Column], Column1 )
SELECT 1,
'Value1'
'Value2'

Notice the lack of comma between Value1 and Value2.

This gives the mentioned error and not a syntax error in the SELECT statement. The reason is that SQL Server concatenates the Value1 and Value2 fields into one column, and therefore the SELECT only produces 2 columns.
SQL Server will be able to concatenate two columns into one without needing to add them together, but only two columns.
So if I were to forget the third comma I would get an “Incorrect syntax near Value2″ error message alerting me to the issue. But forget one comma between 2 columns and you get no such information.

I didn’t see a mention of this in the MSDN documentation.

An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON.

Sep 012008

If you run into the following error message:
An explicit value for the identity column in table ‘<TABLE_NAME>’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
It can mean two things.

One you’ve not enabled identity insert on your table, meaning SQL Server will not let you insert into the Identity column.
This can be rectified with the following statement:
SET IDENTITY_INSERT table_name ON
And then turn it off again when done
SET IDENTITY_INSERT table_name OFF

However it can also mean that you are using for example INSERT INTO, in which cause the message tells you to specify the column names. This means using the following syntax:

INSERT INTO target_able_name (column_name1, column_name2…. column_nameN)
SELECT
YOUR_SELECT_LIST_WHICH_MATCHES_COLUMN_LIST
FROM source_table_name

I wasn’t aware of the latter syntax myself before I got the error message today. But then I learned a little something new today after all :)