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…
http://www.sqlteam.com/item.asp?ItemID=8003
http://msdn2.microsoft.com/en-us/library/aa259221(SQL.80).aspx MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>