CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-cesql-server-express
Panzercrisis
Panzercrisis

Reputation: 4750

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

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?

Upvotes: 4

Views: 5128

Answers (4)

vidyadhar
vidyadhar

Reputation: 3168

i think ' ' represents an empty string which one can't be inserted into a integer value

so you can specify NULL instead of ' '

Upvotes: 0

Raj
Raj

Reputation: 10843

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

Upvotes: 2

John Woo
John Woo

Reputation: 263713

you forgot to mention that the table Clients have a column name Title. The first query works because you have explicitly define the values for the columns you have specified in the statement.

The second one failed because you are using Implicit type of INSERT statement. When using that type of INSERT, you must specify all values for all columns in the table. Since the first column in the table is ID and it is Auto_Incremented, NULL can be used to substitute the value.

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

Upvotes: 1

Habib
Habib

Reputation: 223247

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.

Upvotes: 4

Related Questions