3

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

Community
  • 1
  • 1
ilija veselica
  • 9,414
  • 39
  • 93
  • 147

3 Answers3

2

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.

oleschri
  • 2,012
  • 11
  • 21
  • This seems very complex for large databases... I actually managed to do this (on a small and simple database) using Transactional replication. The problem first time was that I didn't set "Action if name is in use" flag to "Keep existing object unchanged". Default is "Drop existing object and create a new one". – ilija veselica Jul 22 '11 at 09:28
  • @ile (As always) it depends. If you want to have full control over the merge process you can code it in SSIS. But if Transaction Replication can now even cope with schema changes and you have lots of tables to consolidate I can see the advantages of that. – oleschri Jul 22 '11 at 12:08
1

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.

ilija veselica
  • 9,414
  • 39
  • 93
  • 147
  • Looks like SQL Server's Transaction Replication has become far more flexible in 2008 than it was in SQL Server 2000. Would be nice to hear back how it works for you in the long run. – oleschri Jul 22 '11 at 12:02
  • it's still not in the production but I will inform you if I run into new problems – ilija veselica Aug 02 '11 at 13:07
0

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.

Alan Hickman
  • 13
  • 4