Consolidate Sql Server Databases Into 1

I need to consolidate 20 databases that have the same structure into 1 database. I saw this post: Consolidate data from many different databases into one with minimum latency I did

Solution 1:

If I understand you correctly you can do that by

creating an DTS/SSIS package. Here is a basic SSIS tutorial.

or running SQL directly like

 INSERT INTO [TargetDatabase].dbo.[MergedAgency]([Source], [AgencyID], [Name])
 SELECT CAST('DB1' AS nvarchar(16)), [AgencyID], [Name]
 FROM [SourceDatabase1].dbo.[Agency]

 INSERT INTO [TargetDatabase].dbo.[MergedAgency]([Source], [AgencyID], [Name])
 SELECT CAST('DB2' AS nvarchar(16)), [AgencyID], [Name]
 FROM [SourceDatabase2].dbo.[Agency]

Then call either by a recurring SQL Server Job with one Job Step and a Schedule

Don't forget to think about how you detect which row have already been copied to the target database.

Solution 2:

I solved the problem. Now I am using Transactional Replication. In "Publication Properties > Article Properties" I have to set "Action if name is in use" flag to "Keep existing object unchanged". Default is "Drop existing object and create a new one". In SQL 2008 even when I change table scheme these changes are applied to consolidation database.

Solution 3:

SQL-Hub (http://sql-hub.com) 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. It's much easier to use than replication - though not quite as efficient.