Why do I have to include the primary key column in this insert statement?

Question

Given a SQL Server Express Compact Edition database with a table called "Clients" with the following columns:

ID, FirstName, MiddleName, Nickname, LastName, Suffix, IsMale

where ID is an auto-incrementing primary key / identity value, when I try to use this command on the table:

INSERT INTO Clients (Title, FirstName, MiddleName, Nickname, LastName, Suffix,
IsMale) VALUES ('', 'John', '', '', 'Smith', '', 'True')

everything works. But when I try to use this command:

INSERT INTO Clients VALUES ('', 'John', '', '', 'Smith', '', 'True')

this error shows up:

The number of columns in the query and the table must match. [ Number of columns
in query = 7, Number of columns in table = 8 ]

I've been using insert statements with SQL Server for quite a while now, and this has never been a problem; the validation against the number of columns has always ignored auto-incrementing primary keys / identitity types. The only major difference in this case is that I'm now using a very low-end version of SQL Server, whereas I'm used to using much more professional versions. In case it might possibly matter, I am using this in a C# program.

What would be causing this? Thanks.

EDIT: A lot of the focus of this question is the fact that I never have run into this problem when using the non-free, non-compact versions of SQL Server. The SQL Servers I've used before have ALWAYS been smart enough to ignore the identity / primary key column in the column count, and to just figure out the auto-incremented value without me having to spell out specific column names.

Although another difference here is that I've always used SQL Management Studio to mess with table definitions before, whereas I'm going through VS 2012 and SQL Server Compact Edition in this case. The user interfaces involved are obviously pretty different, so I guess maybe there's something being specified for the table or something when you do it in Management Studio that keeps this problem from happening?

Answer 1

If you don't specify the columns in the insert statement then it takes all the columns into consideration. Even if one of the column is auto increment.

Insert - MSDN

column_list must be used when explicit values are inserted into an identity column, and the SET IDENTITY_INSERT option must be ON for the table.

Answer 2

It is possible to write the INSERT INTO statement in two forms.

The first form doesn't specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1, value2, value3,...)

This will work only if the number of values provided match the number of columns exactly

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

This is the form that has to be used, even if there is an ID column of identity type.

Raj

---