Identity_Insert On and Off | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Identity_Insert On and Off

Hi Y’day I was inserting some in rows in table_A then I found that Identity_Insert for Table_B was on so I have to Put Off the Identity_Insert of table_B and then switch on the Identity_Insert of Table_A. I have three questions regarding this:
1) Does SQL Server not allow more than one identity insert on at one shot?
2) Many times I inserted records in tables (having identity column) subsequently but I NEVER faced this kind of issue ever. What can be the reason that I have to manually make Identity_Insert ON an OFF. Thank God there were only 2 tables.
3)Had there been hundreds of table what should have been my approach? Regards,
Ashish Ashish Johri
SQL Server allows IDENTITY_INSERT only for one table at a time. Normally when you insert data into a table with an identity column, you can ignore the identity column because its value will be set automatically. The IDENTITY_INSERT option is for when you are uploading data into an empty table, and the identity column is referred to in a FK relationship for which you also have the data ready, including those specific values. If your script has an SET IDENTITY_INSERT … ON, always add IDENTITY_INSERT … OFF after the insert action.
Check the following article to better understand identity columns… MohammedU.
Moderator All postings are provided “AS IS” with no warranties for accuracy.