How to Use Merge on Linked Servers

SQL MERGE to remote (linked) server table

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 ...

MERGE statement Linked Server

You can use EXECUTE AT to make the merge statement happen on the remote server side.

Put your query in a variable, use ? as placeholder for the parameters and then execute.

Something like this could work for you.

DECLARE @SQL NVARCHAR(MAX) = '
MERGE NOCAdmin.dbo.ViewOnlyTracking AS vot
    USING (SELECT ? AS [Database], ''Copy'' AS Mode, CASE WHEN ? = 0 THEN ''Failed'' ELSE ''Success'' END AS [Status], ? AS Duration, GETDATE() AS DateTimestamp, GETDATE() AS LastSuccessDateTimestamp) AS s
        ON vot.[Database] = s.[Database]
            AND vot.Mode = s.Mode
        WHEN MATCHED THEN
            UPDATE SET          
                vot.[Status] = s.[Status]
                , vot.Duration = s.Duration
                , vot.DateTimestamp = s.DateTimestamp
                , vot.LastSuccessDateTimestamp = s.LastSuccessDateTimestamp

        WHEN NOT MATCHED THEN
            INSERT ([Database], [Mode], [Status], [Duration], [DateTimestamp], [LastSuccessDateTimestamp])
            VALUES (s.[Database], s.Mode, s.[Status], s.Duration, s.DateTimestamp, NULL);';

EXEC (@SQL, @Database, @Passed, @Duration) AT EHRCLSQL01;

How to use merge on linked servers

This is not supported in Microsoft SQL Server 2008. From the msdn documentation,

target_table cannot be a remote table.

You can read more about it here.

Does merge command work with link on different sql servers? (S: Potrgesql, T: MsSQL = MERGE)

Yes you're right, the following code worked in the small table. Giving OleDB link to large tables will solve the situation. Sorry, there is currently no free OleDB provider for PostgreSQL.

    MERGE INTO [MYTEST_STAGE1].dbo.DimOrganizationType AS t USING
      (SELECT ID,
              CompanyID,
              Name,
              LocationTypeID
       FROM POSTGRESQL35W.erp.[ERP].OrganizationType) AS s (ID, CompanyID, Name, LocationTypeID) ON t.ID=s.ID WHEN MATCHED
    AND (isnull(t.CompanyID, 0)<>isnull(s.CompanyID, 0)
         OR t.Name<>s.Name
         OR isnull(t.LocationTypeID, 0)<>isnull(s.LocationTypeID, 0)) THEN
    UPDATE
    SET t.CompanyID=s.CompanyID,
        t.Name=s.Name,
        t.LocationTypeID=s.LocationTypeID,
        t.ModifiedDate=GETDATE(),
        t.ModifiedByUser=@@SERVERNAME+'/'+@@SERVICENAME WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID,
            CompanyID,
            Name,
            LocationTypeID,
            loadDate,
            LoadByUser)
    VALUES (s.ID, s.CompanyID, s.Name, s.LocationTypeID, GETDATE(), @@SERVERNAME+'/'+@@SERVICENAME) WHEN NOT MATCHED BY SOURCE THEN
    DELETE; 

Insert into and merge of data between two SQL Servers on different PCs on the same network

we have solved this problem on many projects and it depends on the scenario of your synchronization, whether it is a manual or automated etc.

In one project, we finally programmed our own synchronization service in .NET. But today we prefer products that offer this functionality out-of-the-box. I personally have experience with DevArt Data Compare, which works great, can be automated and can synchronize data from multiple sources into one (https://www.devart.com/dbforge/sql/datacompare/). In our case, we used it to synchronize from a production server to several test servers via Data Compare and it is orchestrated from one asset.

In addition to Data Compare we also used SymmetricDS (https://github.com/JumpMind/symmetric-ds) which is now unmaintained and is offered in a commercial version (https://www.jumpmind.com/products/symmetricds/overview). It is definitely a good tool but for us the Data Compare from DevArt was better in terms of price and it is able to solve most synchronization scenarios for a good price.

Regards.

What could be the workaround to avoid the MERGE issue i.e. The target of a MERGE statement cannot be a remote table?

If you are not able to change the remote DB structure, I would suggest to build the ClassId mapping table right in the target Class table:

drop table if exists #ClassIdMap;
create table #ClassIdMap (SourceClassId int, TargetClassId int);
declare @Prefix varchar(10) = 'MyClassId=';

insert into targetServer.targetDb.dbo.Classes
    ([Name])
select
    -- insert the source class id values with some unique prefix
    [Name] = concat(@Prefix, Id)
from 
    sourceServer.sourceDb.dbo.Classes;

-- then create the ClassId mapping table
-- getting the SourceClassId by from the target Name column 

insert #ClassIdMap (
    SourceClassId,
    TargetClassId)
select
    SourceClassId = replace([Name], @Prefix, ''),
    TargetClassId = Id
from
    targetServer.targetDb.dbo.Class
where
    [Name] like @Prefix + '%';

-- replace the source Ids with the Name values 

update target set
    [Name] = source.[Name]
from
    targetServer.targetDb.dbo.Class target
    inner join #ClassIdMap map on map.TargetClassId = target.Id
    inner join sourceServer.sourceDb.dbo.Classes source on source.Id = map.SourceClassId;

-- and use the ClassId mapping table 
-- to insert Students into correct classes

insert into targetServer.targetDb.dbo.Students (
    [Name]  ,
    ClassId )
select
    s.[Name],
    ClassId = map.TargetClassId
from
    sourceServer.sourceDb.dbo.Students s
    inner join #ClassIdMap map on map.SourceClassId = s.ClassId;

The problem or risk with this script is that it is not idempotent — being executed twice it creates the duplicates.
To eliminate this risk, it is necessary to somehow remember on the source side what has already been inserted.

---

Related Topics