Suppose these database structure :
--On main server(server)
TicketRequest (id, date, count, start, end/*,and some other columns*/)
Ticket (id, reqId, blockedDate, usedDate, userId/*,and some other columns*/)
--and some other tables
--On linked server(gate)
--Just this table
Ticket (id, userId, blockedDate, usedDate)
A Ticket
may use on gate, so on the gate just its usedDate
may change.
At server, a Ticket
may blocked, deleted; or after importing gate's data, set its usedDate
.
I want to sync them online.
I wrote sp_server_export , sp_gate_import for transport data from server and sp_gate_export , sp_server_import for transport data from gate, to sync them via a file(a server_export file goes to the gate, and server_import file come back to the server).
I used MERGE
and its performance is really good! Now when I'm trying to find a solution for syncing server with gate, after some searches UNFORTUNATELY found Microsoft says : MERGE
not working on remote table as target!
So, how I can sync with a transaction from server?!
NOTE : As our plan, syncing must only execute from server! Gate operator cannot change DB at all!
IMPORTANT NOTE : Consider that concurrency is my first concern! I wanna sync while a user may delete a Ticket
! Or a Ticket
may block at the same time! Or at the gate a Ticket
may use!
UPDATE :
Our syncing logic is :
1 – delete old tickets(which returned with a file syncing and have returnDate
and have modificationDate >
one month later timestamp to ensure delete used tickets from gate)
2 – update gate.ticket where usedDate is null set blockDate = server.blockDate;
3 – insert new rows from server
4 – set transferDate for Tickets transfered to gate ( i mean new/updated tickets in step 2 and 3 )
5 – update server.Ticket.usedDate from gate.Ticket where gate.Ticket.usedDate is not null. ( this will send used tickets from gate back to server )
6 – insert a transferLog ( in transferLog table on server )
Best Answer
If you want to use
MERGE
to sync the table on the remote server, but you can't because it's on the remote server......you should still be able to use separate
UPDATE
andINSERT
statements, for the same effect.Using the same basic criteria as in the
MERGE
, you should be able to:UPDATE
all rows that already exist; andINSERT
any rows that don't already exist.Doing it in that order avoids updating rows you've just inserted.
MERGE
operations do theINSERT
andUPDATE
steps separately in reality, so concurrency issues should be no worse than withMERGE
.Community wiki answer