I have a single server running 4 db instances, each of which has the same schema. Each db instance is tied to a seperate application instance. The goal is to merge the application and database instances into 1 instance of each. Luckily the schemas are identical in the 4 instances. But combining the data is the tricky part. Unique record ID's exist and the main customer table links to several order and sales type tables. I cannot just disable the incoming customer ID field and append the customers to the target shcema for this reason. How do I maintain the referential integrity of a customer to their orders when combining these 2 schemas? I looked at RedGate's SQL Compare but that seems to be for synching a single instance to a single instance.
Any advice is appreciated.