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 didn’t understand all of this so let me ask like this: There are some table who have primary keys but don’t have sourceID, example:
AgencyID Name 1 Apple 2 Microsoft
AgencyID Name 1 HP 2 Microsoft
It’s obvious that these two tables cannot be merged like this, it needs aditional column:
Source AgencyID Name DB1 1 Apple DB1 2 Microsoft
Source AgencyID Name DB2 1 HP DB2 2 Microsoft
If this is the right way of doing this, can these two tables be merged in one database like this:
Source AgencyID Name DB1 1 Apple DB1 2 Microsoft DB2 1 HP DB2 2 Microsoft
…and is it possible to do it with Transactional replication?
Thanks in advance for the answer, it would be really helpful if I would get the right answer for this.
3 Solutions collect form web for “Consolidate SQL Server databases into 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.
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.
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.