-1

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 implemented 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?

Sarah
  • 1
  • 2

2 Answers2

0

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
0

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.

Halim Saad-Rached
  • 154
  • 1
  • 9