Azure synase : merge syntax error

sakuraime 2,316 Reputation points
2020-12-08T11:30:15.587+00:00

46243-capture.jpg

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,335 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Daniel Goucher 1 Reputation point
    2020-12-08T13:28:06.607+00:00

    Hi Sakuraime,

    It looks like you are missing a semi-colon at the end.

    --MERGE (Preview) for Azure Synapse Analytics
    [ WITH <common_table_expression> [,...n] ]
    MERGE
    [ INTO ] <target_table> [ [ 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 ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
    ; -- The semi-colon is required, or the query will return syntax error.

    Read full documentation here: merge-transact-sql

    0 comments No comments

  2. HimanshuSinha-msft 19,376 Reputation points Microsoft Employee
    2020-12-08T22:03:34.7+00:00

    Hello @sakuraime ,

    @Daniel Goucher is correct . but if you miss the ; you get a different error like "A MERGE statement must be terminated by a semi-colon (;)."

    , there is something else also wrong with the query , I am sharing the query which worked for me .

    create table t1   
    (  
    col1 int ,   
    col2 varchar(100)  
      
    )  
      
    INSERT INTO t1 values (1,'testing1')  
    INSERT INTO t1 values (2,'testing2')  
      
    create table t2   
    (  
    col1 int ,   
    col2 varchar(100)  
      
    )  
      
    INSERT INTO t2 values (1,'testingZ')  
    INSERT INTO t2 values (3,'testing3')  
      
    select * from t1   
    select * from t2  
    

    merge into t1 using t2
    on t1.col1=t2.col1
    when matched then
    update set t1.col2=t2.col2
    when not matched by target then
    insert (col1,col2) values (col1,col2);

    Let me know how it goes .

    Thanks Himanshu
    Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

    0 comments No comments