How to turn IDENTITY_INSERT on and off using SQL Server 2008?
Posted By: Anonymous
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.
Solution
Via SQL as per MSDN
SET IDENTITY_INSERT sometableWithIdentity ON
INSERT INTO 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.
Answered By: Anonymous
Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.