I have Database A (which has Tables a, b, c, d) on Server1 and Database B on Server2 (which has tables c, d, e, f). The tables are added/edited/removed at both locations randomly and may change at any time.
Here is my task:
IF tables have identical schemas AND exist at both locations, move only the data from Database A to Database B. Procedure: 1) truncate the tables in the destination (Database B's tables), and then 2) move data from A to B, that is A->B
In this example, tables c and d will be truncated and the data will move from Database A tables to B. Tables e and f are untouched (and tables a and b are ignored).
How will I accomplish this? Do I setup a replication or a similar technology? Everything is on SQL Server 2016.