How to: Merge master/details tables where the primary keys are 'colliding'
How do you handle a situation where you have two databases with an identical master/detail kind of relationship; the master has a unique value primary key (e.g. an auto-identity field) and the details links to this key as foreign key?
You want the sum of master records as the result in the target database. You also want the sum of detail records, but linked to the correct resulting master records, even though those logically same master records are identified by different key values…
A prerequisite for this is that you somehow can make an alternative key for the records. E.g. if you have a Customer table, an alternative primary key may be "CustomerName;StreetAddress1". Even if this is not entirely the case, SQLMergers visual insight might help you to fix those records where there is a need for some manual intervention.
Do:
1. Let’s construct an example with a “Customer” table (master) and “CustomerInventoryItems” table (detail):
CREATE
TABLE
Customer (
ID INT
PRIMARY
KEY
IDENTITY
,
Name
VARCHAR
(
50)
NOT
NULL,
StreetAddres1 VARCHAR
(
50)
NULL,
City VARCHAR
(
50)
);
CREATE
TABLE
CustomerInventoryItem (
ID INT
PRIMARY
KEY
IDENTITY
,
CustomerID INT
NOT
NULL
REFERENCES
Customer(
ID),
ItemName VARCHAR
(
50)
NOT
NULL,
Value
MONEY
NULL
);
Here is a look at the data:
Notice the “Primary Keys Fieldnames(s)” setting. This causes SQLMerger to recognize some of the records as pairs…
2. To let SQLMerger disregard the Id field, fill in “Id” in the “Auto Incr. Field” edit.
(Refresh the Target)
When the tables are merged via the menu “Actions”, “Make Target contain source…”. Click the Merge tab and execute the script. Then return to the Merge tab.
The data will now look like this:
This has to be done before merging the details (the “CustomerInventoryItem” table).
Merging the linked detail records
Here is a look at the details data initially:
If we put in the primary key of this table, this will show (which is quite for no use):
What we want instead is to use the “CustomerName” from the master table and the “ItemName”, as an alternative combined key. To do this we add first a lookup for the “CustomerName”.
3. Select the menu option “Item”, “Add Lookup”, to do this manually
(It could also have been done automatically, but then it would be harder getting the point here.)
This brings up the window shown below:
We have filled in the value we need to have a "Customer.Name" displayed.
4. Now open the target data again.
When the data is opened, this will display:
Notice the new "CustomerID_Lookup" field, in both the source and the target.
5. Enter this ‘virtual’ field as part of the combined alternative primary key for this merge item:
“CustomerID_Lookup;ItemName”
6. And open up the data again:
We’re getting there…
Notice, there is already a line matching.
6. To merge the lines from the source, select the menu option “Actions”, “Make Target contain Source…”.
This is the result:
7. Select the "Script" tab. When the “Script” tab is clicked, this is generated:
8. Click the “Execute Script” button and return afterwards to the “Merge” tab.
The end result is now displayed and we’re finished:
|
|
The final results. We're finished!
|
(I removed the value in the “Auto Incr. Field” edit, so you can see the new ID’s).
Tip:
If we did not want to merge values for the “Value” column, we could just have left it out of the field list in the SQL select statements.
Tip:
If we did want to copy the “Value” field, just not change any existing values in the target database, we can copy the missing values afterwards; a new merge item can be created for this task, if you keep track of which records are new. Or you could simply have altered the values manually to get back to the original value (blue color).
Back to Overview...