Dwain Camps posted an article about using CTE to limit Target table when we apply MERGE.

http://www.sqlservercentral.com/articles/MERGE/97867/

It is a very good article to raise the issue. There is another option to limit the target table for this condition: WHEN NOT MATCHED BY SOURCE with DELETE
with an AND condition to limit the target table rows and also we have another option and the last option to use the condition with UPDATE.
You can find original sample in the article from above link.

Here is the modified sample with the AND condition for DELETE and the operation of UPDATE for the second one:



CREATE TABLE #Test1 (ID INT, RowNo INT, Value decimal(16,2));
CREATE TABLE #Test2 (ID INT, RowNo INT, Value decimal(16,2));

-- target
INSERT INTO #Test1 (ID, RowNo, Value)
VALUES (1,1,25),(1,2,32),(2,1,38),(2,2,61),(2,4,43),(3,1,15),(3,2,99),(3,3,54);
-- source
INSERT INTO #Test2 (ID, RowNo, Value)
VALUES (2,1,45),(2,2,88),(2,3,28);


 BEGIN TRANSACTION T1;

MERGE #Test1 t       -- Target 
USING #Test2 s       -- Source 
ON t.ID = s.ID AND t.RowNo = s.RowNo
  WHEN MATCHED 
    THEN
      UPDATE SET Value = s.Value
  WHEN NOT MATCHED   -- Target
    THEN
       INSERT (ID, RowNo, Value)
         VALUES (s.ID, s.RowNo, s.Value)
WHEN NOT MATCHED BY SOURCE  And t.ID in (Select ID from #test2)
--You cannot access the source ID here and instead we use a subquery 
    THEN
      DELETE
WHEN NOT MATCHED BY SOURCE  
    THEN
      UPDATE SET Value = t.Value*100;

SELECT  *  FROM #Test1  ORDER BY ID, RowNo;

ROLLBACK TRANSACTION T1;


 drop table #Test1 
 drop table #Test2

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s