I have 2 databases on different servers globaly. Both database has these 4 tables in it

1. Orders : id, customerid, orderNumber
2. Products: id, productCode
3. Customer: id, customername
4. Orderdetails: id, quantity, orderId, productId

I want to create reporting table which is a denormalized view (1-2 columns for each table) of these tables from both the databases into a centralized database. For that I first implemtentated transactional replication which replicated the all the columns and data from both the databases into centralized database (worked fine), then i tried to create a trigger on the replicated tables to push the data into the reporting table, but the triggers did not work.

Reporting table : customerId, ordernumber, productcode, customername, Quantity

Is there any recommended way of doing this preferably without triggers?

CodePudding user response：

Is there any recommended way of doing this preferably without triggers?

Write a view that joins the tables together and produces the output you want. Then consider using this view to load a separate table using a scheduled SQL Agent job, or make it an Indexed View.

CodePudding user response：

You can use SSIS packages to migrate data from the 4 transactional replication sources into the target denormalized table. Then deploy the package to SQL Server and schedule a job.