SQL Server Database Consolidation in 1

Question

I need to combine 20 databases that have the same structure into 1 database. I saw this post: Consolidating data from different databases into one with minimal delay

I did not understand all this, so let me ask like this: there is a table that has primary keys but no source identifiers, for example:

Database 1

AgencyID    Name 
1           Apple
2           Microsoft

Database 2

AgencyID   Name
1          HP
2          Microsoft

Obviously, these two tables cannot be combined as follows: this requires an additional 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 to do this, these two tables can be combined into one database as follows:

Source     AgencyID    Name 
DB1        1           Apple
DB1        2           Microsoft
DB2        1           HP
DB2        2           Microsoft

... and can this be done using transactional replication?

Thanks in advance for the answer, it would be very helpful if I got the right answer for this.

Ilija

Answer 1

. . " " > " " ", ", " ". " ". SQL 2008, , .

Answer 2

,

DTS/SSIS. SSIS.

SQL

 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]

SQL Server

, , .

Answer 3

SQL-Hub (http://sql-hub.com) . , , , . , - .