SQL Merge Into with multiple statements

Question

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);
...

FYI, you're reinventing the wheel - *system-versioned* tables have been part of the SQL standard for years are are supported by most RDBMS — Stu, Nov 29 '21 at 11:45
@Stu Did I make such a claim? I asked whether it is possible or not to use multiple insert statements in the same `merge when match`. — Henrique Pombo, Nov 29 '21 at 11:53
@HenriquePombo - FYI just means For your Information, it's not a comment on your Merge statement but given the description of your intention is seems relevant and intended just to help, by all means ignore it. — Stu, Nov 29 '21 at 11:59
Don't see if it's possible in [this documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql). Besides triggering the 2nd insert in the AFTER INSERT trigger. But maybe I'll be proven wrong. — LukStorms, Nov 29 '21 at 12:12
A DML statement can only affect *one* table, not many. If you're opposed to using temporal tables, as @Stu suggests, you'll need to implement triggers. — Thom A, Nov 29 '21 at 12:14
@Larnu, SQL Server has `OUTPUT` clause that makes it possible to insert into two tables in one statement. See my answer — Vladimir Baranov, Nov 29 '21 at 13:03
Sort of, @VladimirBaranov . They are 2 separate DML statements, just nested. But yes, it is still one statement as a whole. — Thom A, Nov 29 '21 at 13:14

score 3 · Accepted Answer · answered Nov 29 '21 at 12:59

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

SQL Merge Into with multiple statements

1 Answers1