Replicate data from multiple tables with same structure to a single destination table with additional columns? 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?
Yes this is achievable in Microsoft SQL Server, and there's a couple different ways you can go about it.

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 &quot;TabCode&quot; filled in accordingly.

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 &quot;TabCode&quot; 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.)