I have a SQL server with a linked server (Non-SQL). The linked server is connected over a WAN and latency can be a problem. It is not a SQL server, so no triggers etc.
I have a view that shows data from the linked server. I want to regularly copy data from the view to a local table.
The view probably has over 15000 records.
When a record is inserted, updated or deleted in the source (linked server) I need this replicating to the local table.
Don't worry about the scheduling etc, I am just concerned with the logic to replicate the data.
Dropping the local table and copying it is not a good answer. I need to somehow go through the source data and replicate changes rather than copying it in bulk.