Post SQL Syntax Error - Merge w CTEs

Question

I'm using Alteryx to cleanse a file and load into a table in SQL Server.  After that process runs i want to run a script to merge the data set into another table.  My script is using a few ctes to set up the SOURCE/TARGET and a MERGE.  This means I'm using both a Begin/End and ending the MERGE with a semicolon.  Alteryx is throwing an syntax error since the semicolon is placed before the END.  This runs fine in SSMS but rejects in Alteryx?  Is there a trick with the alteryx syntax trying to run a merge into with ctes?

Scaled back SQL below

Declare @FieldKey as int

SET @FieldKey = 1

BEGIN

WITH cte1 as (SELECT * FROM tbl)

, cte2 as (select * from tbl)

MERGE INTO cte2

USING cte1 ON cte1.a = cte.2

when matched then

when not match by target then do something else

when not matched by source then do something else ;

END

ALTERYX ERROR

Output Data (18) Executing PostSQL: "query syntax..." : [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ';'.

danilang · Accepted Answer

hi @nmosley1

This is an issue with the ODBC driver issue as opposed to a strictly Alteryx issue.  Apparently, the ODBC driver sees the semi- colon as an end of statement marker and strips it out before passing the query to SQL server.  Normally this isn't a problem, but the Merge statement  has to end with a semi-colon.  To work around it, try escaping the semicolon with a backslash

MERGE INTO cte2
USING cte1 ON cte1.a = cte.2
when matched then
when not match by target then do something else
when not matched by source then do something else \;
END

See here and here for this issue using other applications.

Dan