Azure synase : merge syntax error

Question

Answer 1

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

Answer 2

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