-1

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

Robbie Robertson
  • 31
  • 5
  • 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. – Robbie Robertson Nov 14 '21 at 05:20
  • alright. the newDB is on the different server i.e. [192.168.xxx.xxx].newDB.dbo.tblStudents – Robbie Robertson Nov 14 '21 at 05:21
  • @RobbieRobertson What you're asking for (cross-server `MERGE` and DDL) is not supported by SQL Server, but even if it was Linked Tables and Linked Servers are not intended for that use-case. What are you **actually** trying to do? If you want to synchronize data between two SQL Server instances then you should use proper replication features instead of trying to hack it with `MERGE`. If you want to move data from one server to another then use SSIS. – Dai Nov 14 '21 at 05:24
  • @Dai thanks. the reason to avoid replication is because it's conditional i.e. the data shall only be copied between few tables and only if certain condition are met i.e. status changing, some booleans to true false hence replication wouldn't be very ideal in this case sicne it's only 2 tables. – Robbie Robertson Nov 14 '21 at 05:28
  • @AaronBertrand no, each student can attend multiple classes. The ClassID in tblStudents indicate the same that if I am attending 4 classes then 4 class ids would appear as in 4 rows against me. – Robbie Robertson Nov 14 '21 at 05:30
  • @RobbieRobertson That sounds like a very odd requirement. Anyway, this is something that you should be using SSIS for. – Dai Nov 14 '21 at 05:30
  • So your data isn't normalized? You should fix that first... (Also, ditch the `tbl` prefix. Why are you using it? What year is it?) – Dai Nov 14 '21 at 05:30
  • "in the target the references to parent would be lost since the primary keys are auto generated" - the fact you wrote that tells me that you _know enough to be dangerous_ but don't yet understand SQL Server _enough_ to do the _right thing_ (i.e. you're trying to run before you can walk, in this case you're not familiar with `IDENTITY` seed replication; if you were familiar then you wouldn't have mentioned this). I suggest you stop, take a break, and learn about SSIS _first_, before wasting your time with linked-tables. – Dai Nov 14 '21 at 05:32
  • @AaronBertrand I agree with every bit but the unfortunate thing is that this mess is there for years and it's a database that no one would dare to modify. Hence I am left with very minimum choices :( – Robbie Robertson Nov 14 '21 at 05:47
  • @AaronBertrand the simple INSERT wouldn't be doing what I am looking for. and IDENTITY INSERT even if I do would increase the chances of having existing PKs then. – Robbie Robertson Nov 14 '21 at 05:49
  • I guess you can't add a column to the target table? If you could, you oculd leave the new to old map in there. – Nick.Mc Nov 14 '21 at 06:54
  • @Nick.McDermaid that's the problem that I cannot change the structure in anyway since it's a client db and been using it for ages. Not allowed. – Robbie Robertson Nov 14 '21 at 07:12
  • 1
    As much as I loathe it, the answer probably is in fact SSIS. You can connect to a remote db and insert records one at a time, capture the result with `SCOPE_IDENTITY()` and write them somewhere else. I'm really not a fan of SSIS but it seems like your only option in this case. – Nick.Mc Nov 14 '21 at 07:39
  • The real answer is to execute the whole thing remotely: create a table type on the remote server, pass the whole table in, and execute it there – Charlieface Nov 14 '21 at 13:01

1 Answers1

2

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.

Vadim Loboda
  • 2,431
  • 27
  • 44