Search code examples
asp.netsql-servertransactionsidentity-column

Setting IDENTITY_INSERT off from ASP.Net

I am writing a web based importer to import data from one database to another database. Since the data gets loaded into a table that has an identity, I need to enable Identity_Inserts prior to executing my insert statement. I generate the SQL statements I would need to run into a List, and what I would like to do is set IDENTITY_INSERT to ON prior to looping through the commands, then set it to back off once complete. I am going to execute each command one at a time because I want to keep track of which commands failed.

I tried executing SET IDENTITY_INSERT [TABLE NAME] ON before starting the loop, but this isn't working.

How can I accomplish what I am trying to do, or does anyone have any other ideas?

Solution

  • Taken from https://connect.microsoft.com/SQLServer/feedback/details/517923/set-identity-insert-already-on-error-message-always-returns-dbo-schema-name

    SQL 2k8 BOL states "At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for."

    So I guess, you'll need to set it OFF at the end of the loop?