将不匹配的父记录从一个数据库复制到另一个数据库后，如何将其与现有的子记录映射？

Question

例如：

您有两个数据库:开发和生产，并有相同的表。

现在，假设您有表ProductionDb

1. Products
2. ProductTypes

Products

Id   Name   Type_ID
1    A      1
2.   B      2
3.   C      3

ProductTypes

Id  TypeName
1    Type-A
2    Type-B
3.   Type-C

现在，使用查询将所有这些数据传输到Development db

Insert into DevelopmentDb.dbo.Products
Select Name, Type_ID from ProductionDb.dbo.Products

这使得DevelopmentDb表产品中的数据与ProductionDb's的Products表相同。

考虑到我们假设ProductTypes表在两个数据库中始终保持不变，因此Products表中对ProductTypes的引用键将保持相关性。

但

现在，考虑到产品类型没有相同的信息。

在DevelopmentDb中，ProductTypes有这些行。

ProductTypes

Id  TypeName
1    Type-A
3    Type-B
5.   Type-C
6.   Type-B

注意到了？身份证的变更。

现在，问题是从Product复制到Development中的产品引用ProductTypes可能不完全相同。

现在，为了解决这个问题，我可能会这样做：

在将数据插入产品之前，我将同步并更新ProductTypes，即

INSERT INTO DevelopmentDb.dbo.ProductTypes
Select TypeName from ProductionDb.dbo.ProductTypes ppt
Where NOT EXISTS (Select TypeName from DevelopmentDb.dbo.ProductTypes where TypeName = ppt.TypeName)

会得到新的数据。

但是，在将ProductionDb中的数据插入到DevelopmentDB's Products表中时。如何将复制的数据映射到新创建的ProductTypes，因为要复制的产品中的Type_ID引用的是ProductionDb中的ProductTypes，其中Ids /可能与同步后在ProductTypes中新创建的Ids不同。

如何解决这个问题？

注意:唯一的选择是使用查询而不是复制复制数据。

Answer 1

使用合并操作及其输出来收集映射表。

declare @MappingIds (
    OldTypeId  int  primary key, 
    NewTypeId  int  unique
);

merge into DevelopmentDb.dbo.ProductTypes as target
    using 
    (
        select Id, TypeName from ProductionDb.dbo.ProductTypes
    ) 
    as source on source.TypeName = target.TypeName 

when matched then
    update set                
        TypeName = source.TypeName          

when not matched by target then                                                         
    insert ( TypeName )
    values ( source.TypeName)

output source.Id, inserted.Id        
into @MappingIds (OldTypeId, NewTypeId);


merge into DevelopmentDb.dbo.Products as target
    using 
    (
        select 
             p.Id
             p.Name 
             Type_ID = ids.NewTypeId
        from 
            ProductionDb.dbo.Products p
            inner join @MappingIds ids on ids.OldTypeId = p.Type_ID
    ) 
    as source on source.Id = target.Id

when matched then
    update set                
        Name = source.Name,
        Type_ID = Type_ID 

when not matched by target then                                                         
    insert ( Name,   Type_ID )
    values ( source.Name, source.Type_ID);