Can you perform more than operation in a T-SQL MERGE section?

I’ve been fairly successful with my attempts I need to identify and solidify the OUTPUT columns ( # thereof) and what part of the MERGE statement that #of columns truly correlates to. I am simply asking can I perform multiple operations INSIDE of one MERGE condition like the example below?

WHEN MATCHED
AND TARGET.ProductName <> SOURCE.ProductName 
OR TARGET.Rate <> SOURCE.Rate

THEN 
UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
TARGET.Rate = SOURCE.Rate 

--MAGIC HERE ??
--CAN I ALSO 
--INSERT  in this same section before I do more in below condition?

WHEN NOT MATCHED BY TARGET THEN 

I’ve seen examples for an INSERT done from the content of the OUTPUT section of a MERGE statement, but can what I proposed be done without using another query from the OUTPUT results?

Answer

You cannot multiple operation inside one clause, but you can benefits from the OUTPUT clause to execute further statement.

Based on this Microsoft Documentation:

The OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

The above article contains many example that can helps you.

References

• MERGE (Transact-SQL) (Check the OUTPUT clause section)
• TSQL Merge – Multiple statements after THEN
• The OUTPUT Clause for the MERGE Statements