SQLTeam.com | Weblogs | Forums

Delete linked/cross server table


#1

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

#2

maybe you can have a linked server and perform your delete that way? Since its linked, you can perform the join or sub-query directly and then perform the delete.