Merge Multiple Databases into a Single Database
All, I Have to Create a Single Database Which Has a Basic Schema. This Database Is Going to Be Large (100Gb Plus), and Is to Be Used as a Data Warehouse. Now...
All, I have to create a single database which has a basic schema. This database is going to be large (100GB plus), and is to be used as a data warehouse. Now, the creation of this database is currently being performed in 'one-hit' through a C# code; pulling data from a number of different sources. Due to the sheer volume of data, this creation is causing some problems. It has been decided that instead of creating a single database in one go; to
- Create several smaller databases containing the core data tables.
- Merge the smaller databases into a single larger database.
- Build the schema/add the relevant constraints.
My question is two-fold.
1. What is the best way to merge multiple databases (with identical schemas/table structures)?
2. Is there a better way of going about creating this data warehouse (extremely large database)?
Thanks for your time.
3 Answers
for your first question : You have mentioned identical schema and table structure, in that case, its simply moving of data from one DB table (i.e smaller DB) to another (i.e larger DB). for this have to ensure
1) there was no duplicate of data ( at-least in PK field )
2) move data from one db to another for sql server refer
SQL-Hub () will let you merge multiple databases with the same schema in to a single database. There is a free licence that will let you do this from the UI though you might need to pay for a license if you want to schedule the process to run automatically. However, if there are very big volumes of data it might prove a bit slow for what you want to achieve - you could try it out though.
There are also some articles here that look at some other options for these sorts of problem.
sola's answer is good and will work. Here is another alternative: Don't merge at all. Create a union-all view on top of the multiple base tables that your import creates. That saves you a lot of data movement. Such a view is called a partitioned view.
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
