CODEWITHSUNDEEP
sql-server
carlosm
carlosm

Reputation: 755

SET IDENTITY_INSERT using a linked server on dynamic SQL

I need to insert a record on two tables on different servers.

The table has the same structure on both places and it has an identity column so I need to run a dynamic sql, generate the same id and insert it on both servers.

For example: I'm on [SERVER_A] trying to insert the record into [SERVER_A].MyDB.dbo.Mytable and [SERVER_B].MyDB.dbo.Mytable. I get the last id inserted from both tables and I push the new record using the last id + 1.

So far so good, now I need to use dynamic SQL to set the identity_insert using:

SET IDENTITY_INSERT [SERVER_B].MyDB.dbo.Mytable ON

The only way is to run it on this way:

@Qry = 'SET IDENTITY_INSERT MyDB.dbo.Mytable ON'
EXEC [SERVER_B].MyDb.dbo.sp_executesql ' + @Qry

The problem is that [SERVER_B] will be a variable so I need to put the previous sentence something like this:

EXEC '[SERVER_B]' + '.MyDb.dbo.sp_executesql ' + @Qry

Is there any way to do achieve this? I'm putting this as example but the idea is to push the insert with an specific id so for that I need the IDENTITY_INSERT ON

Upvotes: 1

Views: 1750

Answers (1)

carlosm
carlosm

Reputation: 755

I know it's crazy... but I found a solution:

DECLARE @Qry nvarchar(MAX)
DECLARE @Server nvarchar(50) = '[SERVER_B]'

SET @Qry = '
    DECLARE @Qry2 nvarchar(max)
    SET @Qry2 = N''
    SET IDENTITY_INSERT dbo.MyTable on
    insert dbo.MyTable (id, Name) values (34, ''''AnyName'''')
    SET IDENTITY_INSERT dbo.MyTable off''
    EXEC ' + @Server + '.MyDb.dbo.sp_executesql @Qry2'
EXEC SP_EXECUTESQL @Qry

Upvotes: 2

Related Questions