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:

  • webforms How to bind multiple tables to GridView
  • What options are available for connecting to a Microsoft SQL Server database from an Oracle database?
  • size limit for XML datatype in SQL 2005
  • How to combine EXEC statement results?
  • column values are not showing up
  • Restricting SSRS subscriptions to shared schedules only
  • DataBase 1

    AgencyID    Name 
    1           Apple
    2           Microsoft

    Database 2

    AgencyID   Name
    1          HP
    2          Microsoft

    It’s obvious that these two tables cannot be merged like this, it needs aditional column:

    DataBase 1

    Source     AgencyID    Name 
    DB1        1           Apple
    DB1        2           Microsoft

    Database 2

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.