用except语句更新 [英] Update with except statement
本文介绍了用except语句更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我的查询
SELECT PageVisit_ID,TargetSite_ID FROM [A].Datawarehouse.mi.ctb_PageEvent WITH (NOLOCK)
EXCEPT
SELECT PageVisit_ID ,TargetSite_ID FROM [B].Datawarehouse.mi.ctb_PageEvent WITH (NOLOCK)
来自两个服务器的这两个表.我需要从[B].Datawarehouse.mi.ctb_PageEvent
更新[A].Datawarehouse.mi.ctb_PageEvent
记录中的targetsite_id
仅与上述查询结果匹配.
these two tables from two servers. I need to update targetsite_id
in [A].Datawarehouse.mi.ctb_PageEvent
records from [B].Datawarehouse.mi.ctb_PageEvent
only matched with above query results.
推荐答案
尝试一下
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));
MERGE INTO [A].Datawarehouse.mi.ctb_PageEvent AS Target
USING ( SELECT PageVisit_ID ,TargetSite_ID FROM [B].Datawarehouse.mi.ctb_PageEvent WITH (NOLOCK)) AS SOURCE
ON Target.TargetSite_ID = Source.TargetSite_ID
WHEN MATCHED THEN
//UPDATE OR do nothing
WHEN NOT MATCHED BY TARGET THEN
//INSERT
OUTPUT $action INTO @SummaryOfChanges;
请参考 SQL SERVER –合并操作–单一执行中的插入,更新,删除.我的解决方案只是给出了如何执行此操作的基本思路.最初可能无法100%工作.只要了解了逻辑就可以对其进行调整
Please refer to SQL SERVER – Merge Operations – Insert, Update, Delete in Single Execution. my solution just gives a basic idea on how to do this. It might not work 100% initially. Just tweak it once you understand the logic
这篇关于用except语句更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文