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:

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

---

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.