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

Amit Choudhary

Jul 1 2011 3:16 AM

8.6k

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.