We are doing a multiple database migration into a new single database. The wrinkle here is that one database we are migrating is a copy of one of the other databases we are migrating. At some point in the past, one division took a copy of the database and started using their own copy.

I’ll outline a simplified example of our problem - using just one record type - however we have Account, Contact, Notes, Activities (Calls, Meetings, Tasks), Emails, file attachments etc all thrown in here and related to one another.

What it means now is that there are two input databases to migrate, that have account and contact records that have the same GUID’s.

Imagine that the Sales department has following data in the database:

Sales Database (Initial)

GUID	Name	Rep
123	Alice	Jane
456	Bob	Jill
789	Cindy	Jane

And then it was copied and used by the Service department and became this:

Service Database (Initial)

GUID	Name	Rep
123	Alice	Sue
456	Bob	Sue
012	Diane	Jill

Note that the GUID for Alice and Bob in both databases is the same.

Naturally enough over time, the records for Alice and Bob diverge from each other so there come to be many differences.

Today both databases are in operation onsite at the client, albeit in different departments.

Migrating these into one database is a problem because if we import the Sales database and then import the Service the Rep values will be updated and reflect who the Service Rep is and not the Sales Rep.

We don’t want to lose history of who did what and when, or said what to whom and when.

Thus there must be a better way then just mashing it together and overwriting history.

In our situation we also have a few other considerations:

1. The client is ok if there end up being duplicate Account or Contact records, so long as staff can easily see which records belong to which department 1., we want the client to easily be able to merge records without drama or loss of history
2. We need to run the migration more than once, thus not just an initial INSERT of data, but we’ll do an UPSERT, that is an UPDATE if it’s already migrated or INSERT if added to the old database since the last run of the migration job
3. We (the team doing the migration) want these migration jobs (tasks) to Just Work™

Thusly what we do is map the source database to a new field, and we map the fields that are distinctly different, like Rep, separately.

Thus the import source becomes:

Sales Database (Becomes)

GUID	Name	Sales Rep	Source
123	Alice	Jane	Sales
456	Bob	Jill	Sales
789	Cindy	Jane	Sales

Service Database (Becomes)

GUID	Name	Service Rep	Source
123	Alice	Sue	Service
456	Bob	Sue	Service
012	Diane	Jill	Service

Which when migrated becomes:

New Database (Migrated)

GUID	Name	Sales Rep	Service Rep	Source
345	Alice	Jane		Sales
678	Bob	Jill		Sales
901	Cindy	Jane		Sales
234	Alice		Sue	Service
567	Bob		Sue	Service
890	Diane		Jill	Service

Later when they merge the records, they’ll be able to have:

New Database with Records Merged (Merged)

GUID	Name	Sales Rep	Service Rep	Source
567	Alice	Jane	Sue	Merged
890	Bob	Jill	Sue	Merged
901	Cindy	Jane		Sales
432	Diane		Jill	Service

To bring these databases together is not complex, but it takes time, planning, good testing and above all, patience.