CODEWITHSUNDEEP
sqlsql-servert-sqlmergessis
ChristianProgrammer
ChristianProgrammer

Reputation: 305

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?

Upvotes: 3

Views: 880

Answers (2)

Hadi
Hadi

Reputation: 37348

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

Upvotes: 3

ChristianProgrammer
ChristianProgrammer

Reputation: 305

no... a variable cannot be updated more than once in the same MATCHED clause.

Upvotes: 0

Related Questions