How do you use a variable for the table name in SET IDENTITY_INSERT?

Question

Post reply

How do you use a variable for the table name in SET IDENTITY_INSERT?

tbf

Old Hand

Points: 347
More actions
October 27, 2006 at 6:23 pm

#114898

I have a situation where the name of the table specified in a "SET IDENTITY_INSERT" statement essentially needs to be determined dynamically. Does anyone know of a way to specify the table name as a variable -- or better yet, an expression returned from a scalar function -- in the SET IDENTITY_INSERT statement?
Using EXEC('SET IDENTITY_INSERT ' + @var + ' ON') won't work because the SET IDENTITY_INSERT statement needs to be run in the same scope as subsequent INSERT's.
Right now, for a specific table there are only two possible permutations for the table name, so I can use IF/ELSE logic with hard-coded names. But, I'd prefer to avoid such a solution as it needs to be applied to many, many tables.
TIA... tom

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Put everything in the dynamic sql statement... I'll look more into Monday when I have access to a server.

faizjmc SSC Eights! Points: 881 More actions · Answer 2

create table temp_tab(id1 int identity,id2 int)

insert into temp_tab(id2) values(10)

insert into temp_tab(id2) values(20)

insert into temp_tab(id2) values(30)

delete from temp_tab where id1 =2

--declaring "set identity_insert " dynamically

declare @sql varchar(500), @sql1 varchar(250), @sql2 varchar(250), @tab varchar(50)

set @tab='temp_tab'

set @sql1='set identity_insert ' + @tab + ' on'

set @sql2=' insert into '+ @tab +'(id1,id2)'+ ' values(2,20)'

set @sql=@sql1+''+@sql2

exec(@sql)

select * from temp_tab

-Hope it will be useful.......

Vladan SSC-Insane Points: 22072 More actions · Answer 3

This looks pretty dangerous... "needs to be applied to many, many tables". In my opinion it should be used sparingly, more as a workaround than as a standard. Are you sure that the entire design is correct? You could run into serious problems, because SET IDENTITY_INSERT has certain limitations:

Quote from BOL:

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

tbf Old Hand Points: 347 More actions · Answer 4

This SQL being used to support a development process, not a production database... The process is essentially priming a database with records to be used by a suite of unit tests. In this case, we want to ensure the each and every record in the primed database has a well-known identity.

tbf Old Hand Points: 347 More actions · Answer 5

Thanks faizjmc for jolting my brain back into place... If not for the Friday afternoon haze impeding my thoughts, I'd probably have seen that solution earlier .

tom

Sergiy SSC Guru Points: 110208 More actions · Answer 6

If you rely on "well-known identity" then yes, entire design is definitely wrong.

_____________
Code for TallyGenerator

tbf Old Hand Points: 347 More actions · Answer 7

Given the information posted in this thread, why don't you let me know what your design would look like.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Ninja's_RGR'us

SSC Guru

Points: 294069

October 31, 2006 at 8:50 am

#668996

Lol... can't wait to see that .