Merge SQL Server 7.0 Databases

[mamartin]

All,

I know someone has gone through this same process that I currently face. Hopefully, someone can share some insight/tips. I have 2 SQL Server 7.0 DBs that need to be merged into 1. They have the same structure/schema. It's just 2 different customers who have decided to merge their data into 1 DB. The problem is the identity keys. How can I easily (if that's possible) merge the 2 DBs and still retain referential integrity and also keep the unique identities? If someone knows of a white paper FAQ or any other tip that can be passed on, I would be most appreciative.

Michael A. Martin

 

[dmcmunn]

Sorry, but assuming the identities overlap, the identities will have to be changed if you expect to merge the tables. There's no magic here. It's just plain ole grunt database work.

If you've got the capacity, you could create a merged table, alter it to have an additional identity column that contains the new surrogate key for the "merged" identity, and keep both of the original identity keys in separate columns for backward compatability, if that's what's important for you.

Or, you could find the maximum identity value per table in whichever database you decide to be the one to contain the merger of the two and create a x-ref work table with a NEW IDENTITY column for the source table which begins with the correct offset (past the maximum of the destination table) for it's identity and load it up from the source...make sure the work table column containing the original source identity column is NOT AN IDENTITY, simply a lowly integer. KEEP THIS WORK TABLE.

When you have all the data loaded into work tables, setup a series of UPDATE statements to translate the old identity columns into the new identities and finally SET IDENTITY_INSERT <destination_tablename> ON and pump each of the identity-conformed work tables into their corresponding final destination tables.

After you've verified successful integration and RI, you can backup and discard the work table created above.

Best,
dmcmunn

 

[donutman]

Make all the identity fields on one of the database negatives. Merge the tables with the identity turned off. Turn the identity back on.
-Karl

 

[mamartin]

Thanks to both of you for the suggestions. Right now, I am leaning towards dmcmunn's idea. However, I am curious about the donutman's negative idea. Can you tell me what happens to the negative identity fields once identity is turned back on?

Thanks,
Michael A. Martin

 

[donutman]

Nothing. Negative key values are permitted, but most people never use them. That's why it is likely to work extremely well for you. Just do updates that look like this for all primary and foreign keys:
UPDATE MyTable
Set KeyValue=-KeyValue

What could be easier than that? You don't have to build any cross-ref tables, etc. Of course, if you are already using negative numbers, then it wouldn't work.
-Karl

 

[vongrunt]

Kewl... with negative values it becomes easier to distinguish source of data (< 0 = another database). Plus there is no need to dbcc checkident() afterwards. Perhaps more correct but less flexible way is:

--use src_db
UPDATE MyTable Set KeyValue = 1 + KeyValue + ident_current(target_db.dbo.MyTable)
dbcc checkident(target_db.dbo.MyTable)

Side note: identity columns cannot be UPDATEd.

Another task: how to propagate these changes to other tables in src_db. ON UPDATE CASCADE? Manual UPDATEs when foreign keys are missing?