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:

  • Recursive Query and Count (SQL Server)
  • How do I find running Traces in SQL Server?
  • FreeTDS: How to set charset of parameters running stored procedure
  • SQL Server 2008 management studio intellisense auto refresh (not manually through menu or Keyboard shortcut)
  • is it possible to find out how much of the db data is older than some N years in SQL Server?
  • RESTORE detected an error on page (0:0) in database <database> as read from the backup set
  • 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.

    Ilija

    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.

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