Menu
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. 
203
By default, SQL Server automatically inserts an increment value for an IDENTITY column, when the IDENTITY_INSERT parameter is set to OFF. If you don't need an explicit value for the IDENTITY column, remove the IDENTITY column from the component schema.
Answers. In a given session , you can have only one table's IDENTITY_INSERT property set to ON. You can use set IDENTITY_INSERT state (on/off) only at excute or run time.
To remove the identity from the column entirely is harder. The question covers it, but the basic idea is that you have to create a new column, copy the data over, then remove the identity column. Show activity on this post. The session that sets SET IDENTITY_INSERT is allowed to enter explicit values.
IDENTITY_INSERT off in SQL Server Once you have turned the IDENTITY_INSERT option OFF, you cannot insert explicit values in the identity column of the table. Also, the value will be set automatically by increment in the identity column if you try to insert a new record.
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.
113
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
