Merge Multiple Databases into a Single Database

Question

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

1. Create several smaller databases containing the core data tables.
2. Merge the smaller databases into a single larger database.
3. 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.

Answer 1

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

Transfer data from one database to another database