Does SET IDENTITY_INSERT [Table] ON persist beyond the scope of a SQL Script? I'm wondering if I need to explicitly set it to "OFF" or if SQL Server knows that it should only use that setting for the current script.
Thanks!
views:
301answers:
1
+8
A:
Yes, it does persist beyond the current batch.
It doesn't, however, persist beyond the current session, so if you disconnect immediately after running it, you don't need to change it.
As an aside, it may only be ON for one table at a time, per session, and as pointed out by Aaron in a comment below, will throw an error if you try setting it for more than one table per session.
Jeremy Smyth
2009-06-29 16:39:44
Jeremy: what do you mean by "current batch" compared to current session?
shahkalpesh
2009-06-29 16:45:14
upvoted for session vs. batch diff and how it transitions to the next table.
Raj More
2009-06-29 16:46:04
@Jeremy - your last statement is incorrect. If you try setting IDENTITY_INSERT ON for a second table, you will receive an error: Msg 8107, Level 16, State 1, Line 5IDENTITY_INSERT is already ON for table 'mydb.dbo.MyTable'. Cannot perform SET operation for table 'MyOtherTable'.
Aaron Alton
2009-06-29 18:02:36
Oops, you're right. Edited to suit, thanks.
Jeremy Smyth
2009-06-29 18:13:33
NP. Upvoted the edited version.
Aaron Alton
2009-06-29 18:14:29
Thanks for your answer(s) and comments! I ended up not having to use this for the script I was writing (the database that we inherited from another company didn't have any identity columns set...). It's good to know for the future though!
Pwninstein
2009-06-29 18:48:34