typical way to ensure the destination had the latest data. however, my destination was in another server.
I know this can easily achieve by using linked server but it will ultilize sp_cursoropen, sp_cursorfetch and sp_cursorclose.
I also know i can create a staging table for the data in the destination server; and later on delete the data in the same server.
Question
Is there any method to perform the delete?
declare @source table(col1 int, col2 int)
insert into @source select
1,2 union all select
2,2
declare @destination table(col1 int, col2 int)
insert into @destination select
1,1 union all select
2,1
delete d
from @destination d
where exists(select 1 from @source s
where s.col1 = d.col1 and s.col2 > d.col2)
insert into @destination
select *
from @source