Merge

Transact-SQL MERGE statement transformation details

Syntax comparison

Snowflake SQL syntax:

MERGE
    INTO <target_table>
    USING <source>
    ON <join_expr>
    { matchedClause | notMatchedClause } [ ... ]

Transact-SQL syntax:

-- SQL Server and Azure SQL Database
[ WITH <common_table_expression> [,...n] ]  
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]  
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ <output_clause> ]  
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;  

Example

Given the following source code:

MERGE
INTO
  targetTable WITH(KEEPIDENTITY, KEEPDEFAULTS, HOLDLOCK, IGNORE_CONSTRAINTS, IGNORE_TRIGGERS, NOLOCK, INDEX(value1, value2, value3)) as tableAlias
USING
  tableSource AS tableAlias2
ON
  mergeSetCondition > mergeSetCondition
WHEN MATCHED BY TARGET AND pi.Quantity - src.OrderQty >= 0
  THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
OUTPUT $action, DELETED.v AS DELETED, INSERTED.v INSERTED INTO @localVar(col, list)
OPTION(RECOMPILE);

You can expect to get something like this:

MERGE
INTO
    targetTable as tableAlias
USING
    PUBLIC.tableSource AS tableAlias2
ON
    mergeSetCondition > mergeSetCondition
WHEN MATCHED BY TARGET AND pi.Quantity - src.OrderQty >= 0
    THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
/*** MSC-ERROR - MSCEWI1021 - OUTPUT CLAUSE NOT SUPPORTED ***/
/*OUTPUT $action, DELETED.v AS DELETED, INSERTED.v INSERTED INTO PUBLIC.UNDEFINED_UDF('@localVar(col, list)') /*** MSC-ERROR - MSCEWI1031 - UNDEFINED FUNCTION NOT SUPPORTED ***/*/
/*** MSC-WARNING - MSCEWI1042 - Commented OPTION CLAUSE - THIS IS NON-RELEVANT ***/
/*OPTION(RECOMPILE)*/;