I want to combine two databases in SQL Server 2008 r2. Both databases will be identical in scheme. There will be some data that is identical in each and most likely some data differences.
I would say I am an intermediate user although far from being an expert. Any help getting me on the right track would be much appreciated.
Well I do not know if you just want to merge once for some purpose or you will be needing to keep sync of changes in both databases, either way I'd recommend using a schema/data comparison tool for that. We use a product from RedGate called SQL Compare and it really save us a lot of trouble.
So whenever some developer make changes to their local copy of a database they can compare it to another that is "production-state" so we can generate scripts to sync both. It is pretty straightforward although it is easier if you have properly PK's on your tables (for data comparison that is) either way you can always set up which keys and combinations to use for data comparison. For schema comparison it's even easier.
Just an extra tip, these guys have also a product called SQL Source Control which is exactly that, source control for databases, could save a lot of time too.
Just a note: I do not intend to sell their products, but they are so damn good and had saved us so much time that I had to mention them :)
