SQL Merge Into with multiple statements

I’m trying to keep a historic table of another one. When updating the original I would like to insert rows into the historic one. I’m using Sql Merge:

MERGE TargetProducts AS Target
USING SourceProducts    AS Source
ON Source.ProductID = Target.ProductID
    
-- For Inserts
WHEN NOT MATCHED BY Target THEN
    INSERT (ProductID,ProductName, Price) 
    VALUES (Source.ProductID,Source.ProductName, Source.Price)
    
-- For Updates
WHEN MATCHED THEN UPDATE SET
    Target.ProductName  = Source.ProductName,
    Target.Price        = Source.Price
    
-- For Deletes
WHEN NOT MATCHED BY Source THEN
    DELETE;

Can I make multiple statements in the same “when” condition?, as such:

...
-- For Inserts
WHEN NOT MATCHED BY Target THEN
    INSERT (ProductID,ProductName, Price) VALUES (Source.ProductID,Source.ProductName, Source.Price);
    INSERT INTO anotherTable (OldProductID,OldProductName, OldPrice) VALUES (Source.ProductID,Source.ProductName, Source.Price);
...

Answer

Normally you can INSERT only into one table. The syntax does not allow multiple statements in the same “when” condition.

But, SQL Server has OUTPUT clause which allows to add another table. It is very handy when you need to have some sort of auditing trail.

See this question How to INSERT into multiple tables from one SELECT statement

So, add OUTPUT clause to your MERGE statement. Something like this:

MERGE TargetProducts AS Target
USING SourceProducts    AS Source
ON Source.ProductID = Target.ProductID
    
-- For Inserts
WHEN NOT MATCHED BY Target THEN
    INSERT (ProductID,ProductName, Price) 
    VALUES (Source.ProductID,Source.ProductName, Source.Price)
    
-- For Updates
WHEN MATCHED THEN UPDATE SET
    Target.ProductName  = Source.ProductName,
    Target.Price        = Source.Price
    
-- For Deletes
WHEN NOT MATCHED BY Source THEN
    DELETE

OUTPUT inserted.ProductID, inserted.ProductName, inserted.Price
INTO anotherTable (OldProductID,OldProductName, OldPrice)
;

This will capture both updates and inserts in anotherTable. To capture only inserts you can output at first into a temp table and then filter results by MERGE $action.

Have a look at this question:

Pipes and filters at DBMS-level: Splitting the MERGE output stream