What controls the order of INSERT, UPDATE and DELETE operations in a SQL MERGE statement?

Question

How does SQL Server's MERGE work?

If there are multiple operations to be performed (INSERT,UPDATE,DELETE) then what controls the order in which these operations are carried out?

Answer 1

From MSDN:

For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last.

If I've understood the documentation correctly, SQL Server does not guarantee any order. It will execute your query as it sees fit.

Answer 2

Check out the documentation for MERGE as per my comment.

Within a MERGE statement, you specify how to match records between the source and target tables and what actions to take when there is (or isn't) a match. This therefore determines what records are INSERTed, which are UPDATEed and which are DELETEd.

Take a look at the example from the above documentation page:

MERGE Production.UnitMeasure AS target  
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)  
    ON (target.UnitMeasureCode = source.UnitMeasureCode)  
    WHEN MATCHED THEN   
        UPDATE SET Name = source.Name  
WHEN NOT MATCHED THEN  
    INSERT (UnitMeasureCode, Name)  
    VALUES (source.UnitMeasureCode, source.Name)  
    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;

Here, they are matching records on target.UnitMeasureCode = source.UnitMeasureCode and where there is a match, they update the target record with the Name value from the source. If there is not a match, they insert a new record into the target table using values from the source record.