0

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?

Queen
  • 11
  • 1
  • 1
    Have you read the [documentation](https://msdn.microsoft.com/en-us/library/bb510625.aspx) for `MERGE`? You specify within the syntax of your statement what actions should be taken under what conditions based on what match criteria. Have a read and try to do whatever you're trying to do, then come back if you have specific problems or questions. – 3N1GM4 Dec 22 '16 at 09:34

2 Answers2

0

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.

David Rushton
  • 4,915
  • 1
  • 17
  • 31
  • Isn't this quote referring to how SQL handles any triggers on target tables which have a `MERGE` executed on them, rather than how the `MERGE` itself handles `INSERT`s, `UPDATE`s and `DELETE`s? – 3N1GM4 Dec 22 '16 at 09:35
  • Yes it is, which is a good point. I could not think of any other reason why the order would matter. For most purposes, you can consider it as one operation. But if your db contains a mess of spaghetti triggers *maybe* the execution order could be important. In any case I think your comment, on the original question, is the better response. – David Rushton Dec 22 '16 at 09:41
0

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.

3N1GM4
  • 3,372
  • 3
  • 19
  • 40
  • This doesn't answer the question. It was about the order of operations. So the answer is: however SQL Server feels like it; there is no guaranteed order. – Suncat2000 Dec 18 '18 at 21:14