Dynamic Sql SET IDENTITY_INSERT [Table] ON/OFF problem

Question

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.

Suthish Nair · 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 Nair · Answer

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 Choudhary · Answer

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 Nair · Answer

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

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