Update With Except Statement

This is my query SELECT PageVisit_ID,TargetSite_ID FROM [A].Datawarehouse.mi.ctb_PageEvent WITH (NOLOCK) EXCEPT SELECT PageVisit_ID ,TargetSite_ID FROM [B].Datawarehouse.mi.ctb

Solution 1:

Try this

DECLARE@SummaryOfChangesTABLE(Change VARCHAR(20));

MERGEINTO [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//UPDATEOR do nothing
WHENNOT MATCHED BY TARGET THEN//INSERT 
OUTPUT $action INTO@SummaryOfChanges;

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