Combining data from several MS SQL Server databases into one
I am looking to find a simple solution for the following problem with Microsoft SQL Server:
- I have a set of databases that are all built with exactly the same schema
- they all contain non-overlapping datasets
Now I would like to combine all these separate DBs into one. So basically a stupid copy and paste. However, I am currently thinking about one complication, which is that I need to keep the foreign key relationships unchanged even though all primary keys will change.
So my question regarding this:
- Are there other issues I should consider?
- Are there any tools that can accomplish this simple task, or should I write my own tools / scripts?
Thanks a lot, Alex
source to share
Option 1: Script it in SQL
If you put them on the same sql server, you can write a cross-db query that can fetch from all db and insert rows into one quite easily, but as you say it can get tricky due to problems. such as foreign keys.
Option 2: Using SQL Integration Services
Storing the solution in SQL could mean using something like SSIS and building a migration process, but that would be tricky, no doubt, so you'd be better off creating just a Script.
Option 3: use code on top of DB and ORM
If the db model was complex enough, you might consider using a code based approach and building an ORM model on top of the db.
This would have the added advantage that a copy from one to the other can be done "one entity tree at a time", which allows you to maintain relationships between things without preserving the original ID, as when data moves through it is likely to collide with that the same identifier as in other db's.
Option 4: Export => Import
Export the data from the source db and then import it into the target db, I suppose the SQL server has a wizard for this type of thing, so it can handle it for the whole db, I have a strange feeling though that last time when I tried this it worked really well for a simple flat setup to one table at a time.
How would I solve this
Personally ... my "fastest solution" would be an ORM approach, mainly because I am a developer and have strong C # and Entity Framework skills that I would use for this.
You may prefer
If you are a DBA at heart though you might be better off approaching "write migration Script yourself in SQL approach" (asusming you can put db on 1 server).
source to share
The easiest way is to add an extra system_id field in the values of the federated database (add to all tables): 1-server, 2-server ...
For all foreign keys, add this system_id name and all unique keys (PKs too), and now you can insert data from both systems (inserts set system_id to 1 or 2), no need to create new primary keys (and replace values in subcategories). And in all selects and join you must also use this new system_id field (so the original selection won't work without changing it).
The hard way (for migrations, but easiest to pick) is to add all tables to the system_id field and the PK_original field. In this case, you have to insert the original PK in the PK_original field (and you will have a new PK field with an ID), and in any subcategory for each auxiliary value, you have to replace the original PK value with the newly created portable one. On the other hand, all your favorite source databases will work without the need to change anything.
source to share