(Using SQL Server 2005) I have only a basic knowledge of SQL Server, but need to roll up 5 SQL databases into one database (I guess this is known as "replication"?). These database are of similar structure and make use of an identity
(or autonumber) column. These id's are also referenced in numerous child tables. To avoid collisions, this is my plan:
1. turn off the identity feature of all the columns (but still keep condition of uniqueness)
2. For each database, assign a unique range of identities (ie, db1 gets 1-500, db2 gets 501-1000, etc) and then renumber these columns accordingly. The new id's should cascade to the child tables via the table relationships.
3. Replicate or copy the data of all 5 databases into the new one. There should be no collisions.
4. Turn back on the identity feature of the appropriate columns, hopefully everything works as before.
After this is done we hopefully can get rid of the 5 original databases and proceed with just one.
Should this work and/or is there a better way to do this?
View Complete Post