CODEWITHSUNDEEP
sqlsql-serverdatabase
Alex
Alex

Reputation: 409

Combine data from multiple MS SQL Server databases into one

I am looking to find a simple solution for the following issue with Microsoft SQL Server:

I now would like to combine all those separate DBs in a single one. So basically a stupid copy & paste. However, I currently think of one difficulty, namely that I have to keep the foreign key relations intact, although all the primary keys will change.

So my question regarding this one would be:

Thank you very much, Alex

Upvotes: 3

Views: 1361

Answers (2)

MrZed
MrZed

Reputation: 85

The easiest way is with an extra system_id field in the merged database (add to all tables) values 1-server one, 2-server two ...

To all foreign keys add this system_id field and all unique keys too (PK too), and now you can insert data from both systems (in the insert set the system_id to 1 or 2), no need to create new primary keys (and replace values in sub tables). And in all selects and joins you must use this new system_id field too (so original selects will not work without changing it).

The hard way (for migration, but easiest for selection) is to add all tables the system_id field, and a PK_original field. In this scenario you have to insert the original PK into the PK_original field (and you will have a new PK field with identity), and in any sub table, for every sub value you have to replace the original PK value to the newly created migrated one. But the other side, all your selects from original databases will run without the need to change anything.

Upvotes: 0

War
War

Reputation: 8628

Option 1: Script it within SQL

If you put them on the same sql server you could write a cross db query that can pull from all db's and insert rows in to one easily enough, but as you say that could get complex due to concerns like foreign keys.

Option 2: Use SQL Integration services

Keeping the solution in SQL could mean using something like SSIS and building out a migration process, but that would complex fast no doubt so you'd be better off with just building a script yourself.

Option 3: Use code sat on top of the DB and an ORM

If the db model was sufficiently complex you could 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 another could be done "one tree of entities at a time" essentially allowing you to keep relationships between things without keeping the original id's as when the data moves over it's likely to clash with the same id's used in other db's.

Option 4: Export => import

Export the dat afrom the source db and then import in to the destination db, I believe SQL server has a wizard for this type of thing so may be able to handle doing this for a whole db, I have a funny feeling though that last time I tried this it only really worked well for a simple flat set in to one table at a time.

How I would solve this

Personally ... my "quickest solution" would be the ORM approach, mainly because i'm a developer and have both strong skills with C# and Entity Framework that I would use to do this.

You may prefer

If you are a DBA at heart though you may be better suited to the "write a migration script yourself in SQL approach" (asusming you can put the db's on 1 server).

Upvotes: 4

Related Questions