1

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?

1 Answer 1

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.)

7
  • Thanks, so create a indexed view on the table and then use the indexed view as the source table?
    – unlimit
    Nov 11, 2020 at 3:20
  • @unlimit Yes, so if you have each source table in the same database (which can be accomplished multiple ways, one being Replication), you can create an Indexed View that UNION ALLs those source tables, with the additional TabCode column. The Indexed View is now the combined dataset of all of your source tables as you required.
    – J.D.
    Nov 11, 2020 at 3:22
  • Can I create indexed views in each of the databases and use for replication? So db1.tableA, db2.tableA each has its own indexed views and they are used for replicating to dbDestination.tableX?
    – unlimit
    Nov 11, 2020 at 3:46
  • @unlimit You won't be able to use Replication to combine each source Indexed View into one table on the target database (SSIS would be a better approach for that). But if you want to use Replication + Indexed View, then I would recommend thinking about it the other way around. Use Replication to copy each source table (db1.tableA, db2.tableA, etc) to the target database (if the table names are exactly the same then you'll need to use a pre- and post- replication script to rename them after they replicate, and it might be better to consider SSIS). After the tables are in the target database...
    – J.D.
    Nov 11, 2020 at 3:56
  • 1
    @J.D. No one in the future will care what you originally got wrong. By all means improve your answer over time but in-line edit history is counterproductive see meta.stackexchange.com/questions/127639/…. By the way, cross join isn't allowed in an indexed view either.
    – Paul White
    Nov 21, 2020 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.