SETTING IDENTITY INSERT ON AND OFF via a String

I'm currently doing a number of stored procedures that modify existing tables with new and changed data. In order to get around the Identities on these tables, I had been using the set identity insert method to allow access to the tables. I then tried to make the code useable over a number of systems, by adding in DB variables. So to run the identity snippet - I tried to use the below method - which doesn't work. Does anyone have any other ideas that will allow this to work?

SET @sSQL = 'SET IDENTITY_INSERT ' + @targetDBName + '.dbo.tblblah OFF '

EXEC (@sSQL)

--Do some Stuff

SET @sSQL = 'SET IDENTITY_INSERT ' + @targetDBName + '.dbo.tblblah OFF '

EXEC (@sSQL)

Thanks in advance,

Jamie
j_young_80Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
try this (note that you have to turn the identity insert ON before trying to insert a value into the identity field, and not OFF...
SET @sSQL = 'USE ' + @targetDBName + '    SET IDENTITY_INSERT dbo.tblblah ON  '
EXEC (@sSQL) 
--Do some Stuff 
SET @sSQL = 'USE ' + @targetDBName + '    SET IDENTITY_INSERT dbo.tblblah OFF '
EXEC (@sSQL)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
j_young_80Author Commented:
This doesn't seem to work. I get the error;
Cannot insert explicit value for identity column in table 'tblTeam' when IDENTITY_INSERT is set to OFF.

It seems as though executing a SET statment in a stored proc doesn't work with exec?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you clarify a bit what your "--- do some stuff" is doing ie how it is doing it?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

BrandonGalderisiCommented:
Your insert statement must list the columns...

you have to do

insert into yourTable(field1, field2, field3, etc...)
select field1, field2, field3, etc...
from your other table

and not

insert into yourTable
select field1, field2, field3, etc...
from your other table
0
LowfatspreadCommented:
exec(@string)   is processed in its own context and doesn't benefit from the set identity insert on ....

set @str =' set identity insert on insert into table select ... set identity insert off'
exec(@str)
0
LowfatspreadCommented:
sorry basically what angeliii was hinting at
0
j_young_80Author Commented:
sorry it took so long to get back - I was running an insert statement in the "Do some stuff" section.

I added the identity insert statement on and off  into the same string as the insert, and executed it all at once and that seemed to solve the problem. I don't understand why this should make a difference however? You say it's run in its own context but i thought the identity_insert was a global statement - i.e it will stay on throughout until you turn it off?

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>but i thought the identity_insert was a global statement
yes, that is true, according to the docs and also my experience...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.