Merge WHEN NOT MATCHED BY SOURCE

Question

MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate 
THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate 
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET 
THEN INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target and same record does not exist in source then delete this record target
WHEN NOT MATCHED BY SOURCE 
THEN DELETE 
--$action specifies a column of type nvarchar(10) in the OUTPUT clause that returns 
--one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE' according to the action that was performed on that row
OUTPUT $action, 
DELETED.ProductID AS TargetProductID, 
DELETED.ProductName AS TargetProductName, 
DELETED.Rate AS TargetRate, 
INSERTED.ProductID AS SourceProductID, 
INSERTED.ProductName AS SourceProductName, 
INSERTED.Rate AS SourceRate; 

I am learning usage of Merge statement. please tell me what does it mean WHEN NOT MATCHED BY SOURCE
and WHEN NOT MATCHED BY TARGET

anyone can explain with a easy example that what is the meaning of WHEN NOT MATCHED BY SOURCE
and WHEN NOT MATCHED BY TARGET

Thanks

Answer 1

WHEN NOT MATCHED BY TARGET always results in an INSERT. That is, this is for rows that exists in the source, but are not in the target table.

WHEN NOT MATCHED BY SOURCE most often results in a DELETE, but it can also lead to an UPDATE. These are rows that exists in the target table, but which is not in the source. This branch is less commonly used, as in most cases, you simply want rows in the source that are not in the target to be left unchanged.

Here is a simple example that you can play with. The target table initially has the name of the numbers in Finnish, while the source table has them in English. This particular MERGE, causes target to be equal to source, which is maybe a little funny. But it is a short example.

CREATE TABLE target (id   int           NOT NULL PRIMARY KEY,
                     name nvarchar(20)  NOT NULL)
CREATE TABLE source (id   int           NOT NULL PRIMARY KEY,
                     name nvarchar(20)  NOT NULL) 

INSERT target(id, name)
   VALUES(1, 'Yksi'), (2, 'Kaksi'), (4, 'Neljä'), (6, 'Kuusi')
INSERT source (id, name)
   VALUES(1, 'One'), (2, 'Two'), (3, 'Three'), (5, 'Five')

MERGE target t
USING source s ON t.id = s.id
WHEN NOT MATCHED BY TARGET THEN
  INSERT (id, name) VALUES(s.id, s.name)
WHEN MATCHED THEN
  UPDATE SET name = s.name
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
OUTPUT $action, inserted.id, inserted.name, deleted.id, deleted.name
;

SELECT id, name FROM source
go
DROP TABLE target, source