Azure synapse Merger syntax error for Merge into TSQL

Question

Just would like to know what's the problem of the following merge .

thanks

Answer 1

As at today, MERGE is supported in Azure Synapse Analytics dedicated SQL Pools and in preview. Simply terminate your statement with a semi-colon, eg

MERGE INTO t1 USING t2
ON t1.col1 = t2.col1
WHEN MATCHED 
    THEN UPDATE SET t1.col2 = t2.col2
    
WHEN NOT MATCHED 
    THEN INSERT ( col1, col2 )
    VALUES ( col1, col2 );

Also ensure your target tables are HASH distributed in order to avoid the following error:

Msg 100087, Level 16, State 1, Line 41 Merge statements with a WHEN NOT MATCHED [BY TARGET] clause must target a hash distributed table.

See here for a full repro script. See the announcement here for more details:

https://azure.microsoft.com/en-gb/updates/new-merge-command-for-azure-synapse-analytics/

Answer 2

Faced a similar issue recently. The MERGE code was correct. All I did was recreate the Target(destination) table along with Hash distribution on primarykey column.

Something like this:

CREATE TABLE [SchemaName].[TableName]
( 
    [Id] [int]  NOT NULL,
    [Column1] [nvarchar](50)  NOT NULL,
    [Column2] [datetime2](7)  NOT NULL,
    [Column3] [bit]  NOT NULL
)

WITH
(
    DISTRIBUTION = HASH ( [Id] ),  --(hash distributed table on this Id column)
    CLUSTERED COLUMNSTORE INDEX
)

The Merge code worked.