Beginner Beginner - 3 months ago 20
SQL Question

How to turn IDENTITY_INSERT on and off using SQL Server 2008?

Why am I getting an error doing an insert when

IDENTITY_INSERT
is set to OFF?

How do I turn it on properly in SQL Server 2008? Is it by using SQL Server Management Studio?

I have run this query:

SET IDENTITY_INSERT Database. dbo. Baskets ON


Then I got the message back in the console that the Command(s) completed successfully.
However when I run the application, it still gives me the error shown below:

Cannot insert explicit value for identity column in table 'Baskets' when
IDENTITY_INSERT is set to OFF.

gbn gbn
Answer

Via SQL as per MSDN

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT sometableWithIdentity (IdentityColumn, col2, col3, ...)
VALUES (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF

The complete error message tells you exactly what is wrong...

Cannot insert explicit value for identity column in table 'sometableWithIdentity' when IDENTITY_INSERT is set to OFF.

Comments