Dynamic Sql "SET IDENTITY_INSERT [Table] ON/OFF" problem

Amit Choudhary

15y11.8k

Hi Greetings,

I'm Creating an dynamic SQL which inserts data from one DB to another DB having same Schema. For tables which are having Identity Column had to turn the IDENTITY_INSERT option ON. But its seems like its not working. The statement is executing successfully but Insert statement is giving error of Identity insert.

     SELECT @Sql = 'SET IDENTITY_INSERT '+@DBName+'.[dbo].'+QUOTENAME(@tablename)+' OFF; '+
        'SET IDENTITY_INSERT '+@DBName+'.[dbo].'+QUOTENAME(@tablename)+' ON; '+
        'INSERT INTO '+@DBName+'.[dbo].'+QUOTENAME(@tablename)+' Select * from '+@SDBName+'.[dbo].'+QUOTENAME(@tablename)+'; '+
        'SET IDENTITY_INSERT '+@DBName+'.[dbo].'+QUOTENAME(@tablename)+' OFF; '
        EXEC (@Sql)

I'm using sql server 2005 Any Idea or help?

Your response would be highly appreciated.

Thanks.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suthish Nair15y ago
Accepted answer

declare @Sql nvarchar(max), @tablename nvarchar(max) = 'Table1'

set @Sql = 'SET IDENTITY_INSERT ' + QUOTENAME(@tablename) + ' ON; ' + CHAR(13) +
'INSERT INTO ' + QUOTENAME(@tablename)+' ([id], [name]) VALUES (100, ''TEST100''); ' + CHAR(13) +
'INSERT INTO ' + QUOTENAME(@tablename)+' ([id], [name]) VALUES (300, ''TEST300''); ' + CHAR(13) +
'SET IDENTITY_INSERT '+ QUOTENAME(@tablename) + ' OFF; '
--PRINT @Sql
EXEC sp_executesql @Sql

I forgot to mention, the key point here is the column names. You need to pass the column names during identity insert.
Suthish Nair15y ago
Because while using dynamic sql, EXEC statement needs everything within its SCOPE. Do a search for "EXEC SET IDENTITY_INSERT on" you will get information. I don't have working sample right now.. instead of SELECT try using SET command.
Amit Choudhary15y ago
Thanks for response Suthish. I was expecting you :)
Well My problem is little different i'm doing the same as Abhimanyu mentioned but in a different way.
I'm trying to execute the dynamic sql where we create sql as string and then Execute it using the
Exec() method in Sql Server.

and in this case the Set IDENTITY_INSERT [dynamic table name] ON; is not working not even giving any error.
Suthish Nair15y ago
Check this thread:

SQL Server Fun - 4 :: How to insert duplicate values to identity column