3

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

  • I have a set of databases, which are all build with exactly the same schema
  • they all contain disjoint sets of data

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:

  • Are there other issues I have to consider?
  • Are there some tools which can perform this simple task or do I have to write my own tools / scripts?

Thank you very much, Alex

Improve this question
1
  • One other option that might be worth considering, as to not break anything that currently exists, is to keep the existing messed up structure, and simply create a SQL view of the data to query against. There's a slight performance hit this route, but significantly less risky than actually moving data. Commented May 5, 2017 at 14:23

2 Answers 2

Reset to default
4

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.