What could be the workaround to avoid the MERGE issue i.e. The target of a MERGE statement cannot be-CodePudding

Copying data from one table to another both on different servers but similar structures.

Ended up on this.

declare @ClassIds table (OldClassId int, NewClassId int);


merge into newDB.dbo.tblClasses as target
    using
    (
        select
            Id = Id * (-1),
            [Name]  
        from
            oldDB.dbo.tblClasses
    )
    as source on source.Id = target.Id

when not matched by target then
    insert ([Name])
    values (source.[Name])

output source.Id * (-1), inserted.Id      -- ← the trick is here
into @ClassIds (OldClassId, NewClassId); 


insert into newDB.dbo.tblStudents
select
    s.Id,
    s.[Name],
    ClassId = ids.NewClassId
from
    oldDB.dbo.tblStudents s
    inner join @ClassIds ids on ids.OldClassId = s.ClassId;

but error:

The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.

Workaround could be reversing i.e. target and server but that's not ideal in my situation.

What should I do?

Original question:

Original question

Reason to do this:

the reason is I am copying the parent-child data and in the target the references to parent would be lost since the primary keys are auto generated hence in target a new record in parent would generate new Id but child would have the old parent id as of the source hence lost. So to avoid that the merge would make sure tyo update the child record with new parent ids.

edit:

the newDB is on the different server i.e. [192.168.xxx.xxx].newDB.dbo.tblStudents

CodePudding user response：

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.