I have 3 databases on a SQL server instance
- DataBase_A
- DataBase_B
- DataBase_C
Each database have 3 tables, that are identical in their schema, but different in their data content
- Table 1
- Table 2
- Table 3
I need to set up replication of the 3 tables from all 3 databases to a single agregate datatabase - DataBase_Combined on the same server instance, containing all those 3 tables agreagating each its respective data.
When I set up the replication (used snapshot replication type)
I have 3 publication
- Database 1_combined
- Database 2_combined
- Database 3_combined
I see that the data gets overwritten in the destination DataBase_Combined by each subsequent snapshot.
So, the Table 1 would only have the data from the Database 3, because its snapshot ran last.
Looking into the snapshot folder configured, in the file for a particular table - Table 1.sch, I see that there are statements to drop a table and then create it.
This would explain to me why the data gets overwritten.
Question 1: Is my understanding correct in this regard ? Or am I off ?
Question 2: How can I set up the replication correctly to aggregate the data as outlined above ? Ideally, I need the task to run every hour.