Replicate data from multiple tables with same structure to a single destination table with additional columns?

All we need is an easy explanation of the problem, so here it is.

I have a table with say the following structure. This table is in multiple databases.

Name varchar(50),
Age int

I want to replicate the data from this table from multiple databases to a single table. The target table has an extra column TabCode. I want to populate this TabCode with a value depending on from where the data is coming.

I do not have the option of adding any new column to the source tables.

Name varchar(50),
Age int,
TabCode int

Can this be done in Microsoft Sql Server?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Yes this is achievable in Microsoft SQL Server, and there’s a couple different ways you can go about it.

  1. SSIS Package: You can create an SSIS package that pulls the data from the different source databases and consolidates it all into one table on the target database with the additional column "TabCode" filled in accordingly.

  2. Replication + View: You can setup replication publications between your source servers to subscriptions on your target server. Then you can use a view that unions all the data together into one view with the additional column "TabCode" filled out.

Some of the things to consider about SSIS packages is that they require some upfront implementation (you create them as a Visual Studio SSDT project type), and they require a job to trigger when they run (typically a SQL Agent job). So they aren’t necessarily very real time in data synchronization (though they can be scheduled to ran as frequently as every second – but their actual runtime will depend on how you implement the SSIS package).

Some things to consider about Replication + Views is you can accomplish near real time data synchronization (with Transactional Replication) and they require less upfront implementation, but can be a little tricky getting setup first time around. (Overall they’re not too bad to setup and work well when they work.)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply