SQL MERGE to remote (linked) server table

Question

Is it possible to utilize the SQL MERGE function on a linked server's database table? The end goal is to synchronize the remote table with our local SQL server table. I’ve done some research online and couldn’t find any related information. If it is possible how would you setup the source and target statements?

Do you mean that the table is on a [Linked Server](http://msdn.microsoft.com/en-us/library/ms188279.aspx)? Where is the *target* table located? From [Merge](http://technet.microsoft.com/en-us/library/bb510625.aspx): *"target_table cannot be a remote table."* — Mikael Eriksson, Mar 06 '13 at 06:33
Then you can not use `merge` if you want to merge from `CT` to `FL`. — Mikael Eriksson, Mar 06 '13 at 14:02
You could reverse the job by using a stored procedure on the remote server merging from where you are. You have to setup the reverse link of course. — Mikael Eriksson, Mar 06 '13 at 15:13
calling the MERGE from the remote server would be preferred anyway, even if you could call it from the local server, since performance on INSERT is so much better locally than remotely. — Geoff Griswald, Mar 19 '21 at 14:06

score 23 · Accepted Answer · answered Nov 06 '13 at 17:39

23

To reiterate the comment by @Mikael Eriksson, yes, you can. The target of a MERGE cannot be remote, but the source of a MERGE can be remote. So, if you can run the MERGE statement from your server in FL, then it is quite possible. For example, you could run something like this on your remove server in FL:

MERGE INTO "local FL table" USING "CT server"."database"."schema"."same table" ON ...

answered Nov 06 '13 at 17:39

Michael Painter

254
2
2

Thanks for mentioning that, i thought i hit a dead end when i got the error about the merge not being able to run with remote targets, but then i got the hint from your answer that i can swap servers and execute the query on the main server itself, and it worked like a charm. – Niklas May 21 '17 at 05:40

score 6 · Answer 2 · answered Mar 11 '13 at 16:02

6

Apparently my research wasn't good enough, it’s stated right on MSDN: “target_table cannot be a remote table” ... so that answers this question...

answered Mar 11 '13 at 16:02

devHead

794
1
15
38

1

If I am not going to use it for a remote table, its half worthless! It's like travelling 1000 miles route and having to leave your car and start walking after the 500th mile.. – Subliminal Hash Apr 25 '17 at 09:09
1

If you look up the docu for merge(https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017), the target can not be a remote table. The source can however be a remote table. So, you can 'pull' data from a source, but you cannot 'push' data to a target. – rrozema Apr 26 '18 at 11:35

score 0 · Answer 3 · answered Jan 12 '22 at 05:35

This is not an exact Answer of this Question. But an Alternate for Updating remote Table in the Linked Server.

UPDATE [RemoteItem] 
SET    [RemoteItem].[AccountID] = [I].[AccountID] 
FROM   OPENQUERY
(
    [LINKEDSERVER],
    'select AccountID from [RemoteDb].dbo.[Item]'
) [RemoteItem] 
INNER JOIN [LocalDB].[dbo].[Item] AS [I] 
    ON p.ProductID = oq.ProductID

Refere this Link

Not Tested.

score -1 · Answer 4 · answered Mar 13 '14 at 16:41

Yoy can always use EXEC('SQL CODE HERE')AT YOUR_LINKED_SERVER in your server, maybe as a Stored Procedure.

This will execute the query you want on your linked server so you could merge a local table (target_table) with a server table (source).

This is a code I use in a Stored Procedure in my Server that its called from the client. Client exec stored procedure in server->Server Exec Query to update different linked servers (clients) with the same informacion (employees)

    EXEC('
SET IDENTITY_INSERT PVBC.DBO.empleadas ON

MERGE INTO PVBC.DBO.empleadas A
USING(
    SELECT id_empleada, nombre, apellidos
    FROM SERVIDOR.PVBC_SERVIDOR.DBO.empleadas) TA
ON (A.id_empleada =TA.id_empleada)
WHEN MATCHED THEN
UPDATE 
    SET A.nombre=TA.nombre,
        A.apellidos=TA.apellidos
WHEN NOT MATCHED THEN

    INSERT 
        (id_empleada, nombre, apellidos)
    VALUES
        (id_empleada, nombre, apellidos);

SET IDENTITY_INSERT PVBC.DBO.empleadas OFF
')AT MEGA --This is one of my linked servers

but this requires that the target server must have the source server as a linked server, which isn't always possible or desirable. In a way, this just flips the problem around. — underscore_d, Aug 14 '17 at 09:57
@EdgarHolguin Just to remind you, if the RPC calls aren't enable for remote servers the above won't work — ha9u63a7, Oct 20 '17 at 08:41
Works perfectly, not sure why you're getting downvoted. This is an ideal solution. Obviously the server have to be linked and RPC has to be set up, but if you're not able to get those basic prerequisites set up, then you probably shouldn't be trying to merge cross-server in the first place. — Geoff Griswald, Mar 19 '21 at 14:12

SQL MERGE to remote (linked) server table

4 Answers4

Linked