Mapping Not-Matching Parent Records After Database Migration

When migrating data from one database to another, it’s common to encounter discrepancies in the parent-child relationships. In this article, we’ll explore a scenario where you’ve copied matching records from the production database to the development database and now need to map the non-matching child records to the newly created parent records.

Background

Let’s start by examining the provided example. We have two databases: Development and Production, both with identical tables Products and ProductTypes. The data is as follows:

Production Database

ProductTypes

Products

We’ll assume that the ProductTypes table remains unchanged in both databases. However, when we copy the data from Production to Development, we notice changes in the ID values.

ProductTypes (Development Database)

The Problem

The issue arises when we need to insert the copied data into the Products table in the Development database. We have to map the child records (Type_ID) to their corresponding parent records (TypeName). Since the IDs are different, we cannot directly reference the same record.

Solution: Merge Operation and Mapping Table

To solve this problem, we can use the merge operation, which allows us to compare records in both databases and update or insert them accordingly. We’ll create a mapping table to collect the necessary information for the insert operation.

First, let’s define our mapping table:

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

We’ll use two merge operations: one for ProductTypes and another for Products.

ProductTypes Merge Operation

We’ll start by merging the ProductTypes tables:

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);

In this operation:

• We join the ProductionDb.dbo.ProductTypes table with the DevelopmentDb.dbo.ProductTypes table on the TypeName column.
• When a match is found in both tables, we update the existing record with the new TypeName.
• If no match exists, we insert a new record with the TypeName value.

The output of this operation is stored in our mapping table, which will be used later for further merges.

Products Merge Operation

Now that we have an updated list of ProductTypes, let’s merge the Products tables:

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);

In this operation:

• We join the ProductionDb.dbo.Products table with our mapping table (@MappingIds) on the Type_ID column.
• When a match is found in both tables, we update the existing record with the new Name and Type_ID.
• If no match exists, we insert a new record with the Name and Type_ID values.

Conclusion

In this article, we explored the challenges of mapping non-matching child records after database migration. By using the merge operation and creating a mapping table, we can efficiently update or insert these records to maintain accurate relationships between parent-child tables.

Remember to always back up your databases before performing any data migrations or updates.

Last modified on 2025-04-13