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 source ID, example: Data Base 1 what are you trying to accomplish (application consolidation, reporting, warehousing)?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.INSERT INTO [Target Database][Merged Agency]([Source], [Agency ID], [Name]) SELECT CAST('DB1' AS nvarchar(16)), [Agency ID], [Name] FROM [Source Database1][Agency] INSERT INTO [Target Database][Merged Agency]([Source], [Agency ID], [Name]) SELECT CAST('DB2' AS nvarchar(16)), [Agency ID], [Name] FROM [Source Database2][Agency] This seems very complex for large databases...I actually managed to do this (on a small and simple database) using Transactional replication.It's much easier to use than replication - though not quite as efficient.
Default is "Drop existing object and create a new one"[email protected] (As always) it depends.Will the 20 databases still be used after this process is over?Also, what are the approximate sizes of these databases as well as the number of tables in each?Backups and replicating the database are bigger and slower, probably. Some background: The two databases are the "gold source" for their respective data.
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.