March 5, 2009 at 2:46 pm
I have a copy of a MS KB that indicates you first need to run
'set identity_insert tender ON'
before you can insert values back into the TENDER table
It indicates you first run the set identity_insert TENDER ON query, then you run a separate query to populate records in that same table:
insert into tender (id, description, additionaldetailtype, displayorder,
code)
values (1, 'Cash', 1, 1, 'CS')
The problem is I keep getting an error mssg indicating that "Cannot insert
explicit value for identity column in table 'Tender' when IDENTITY_INSERT is
set to OFF." - - in other words, it appears to reset back to OFF right after
I run the "set to ON" query
But it works when I run both on the same query:
set identity_insert tender ON
go
insert into tender (id, description, additionaldetailtype, displayorder,
code)
values (1, 'Cash', 1, 1, 'CS') - - this succeeds....
Just wanted to run this through some more experienced SQL users - is my
syntax correct? Am I jacking up the table schema with my syntax? Does it
re-set to OFF after I run this, or do I need to manually set to OFF?
Thanks for helping this sql_newbie....
March 6, 2009 at 1:48 am
I think this SET option is only for the scope of the current session. BOL says you cannot have more than one table with identity_insert on and you will get an error if you try in the same query session, but open a seperate query window and you can.
BOL is not clear about whether you need to explicitly turn it off but if you close the session and open a new one you do need to reset it (as you have found).
March 9, 2009 at 10:57 am
A note on "sessions". In SSMS, a session is one continuously open query window or, in other SQL apps, one continuous connection. You can't do Identity Insert in two separate windows or two separate queries (such as 2 different stored procedures or SSIS tasks). It all has to be done in the same window at the same time. Once you close a window, you've "closed" your session.
And it is always a good idea to cleanup after yourself by setting Identity_Insert OFF right after you're done with your insert. That way you won't get any accidental conflict with another session.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply