SQL Server 2005: Getting error when setting auto increment on one of two primary keys

Question

hi

I am getting an error "Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF."

The table has two primary key, one is set to auto-increment, one is set with value i passed into.

Is this possible in SQL Server 2005? or am i missing something?

Thanks alot in advance.

Answer 1

You are trying to insert a value into an identity column You can do this by

SET IDENTITY_INSERT TableName ON
INSERT INTO TableName() VALUES ()
SET IDENTITY_INSERT TableName OFF

http://msdn.microsoft.com/en-us/library/ms188059.aspx