Identity_Insert

[HLPCH]

I have a stored Procedure with couple of Insert statements.

Insert into Contact1(field1,field2)values(value1,value2)
Insert into Contact2(field1,fields2)values(value1,value2)

Both the tables contact1 and contact2 have an Identity field.

My insert fails because at a given time I could only set
one IDENTITY_INSERT On .

Is there any way to work around this problem?

Thanks

 

[Catadmin]

I have two thoughts on the matter. Both are kind of icky, but one of them might work.

1) Do an Alter Table statement in your proc to temporarily remove the identity value of the column in question, run the insert, then do another Alter Table statement to reset the identity value

2) Seperate your Proc into two seperate Procs. Set Identity_Insert On for the first table and do your first table processing (keeping any necessary info for table2 processing in variables), then Set Identity_Insert Off, call the second Proc and pass any needed info to it via the variables.

Though, if you're going to be doing this a lot, why do you even have identity enabled? Maybe you should re-think your table design??



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[NoCoolHandle]

No.

You will need to code it to turn it on and off for each statement. Not a bad idea for error cheking etc anyway.