MERGE INTO Azure synapse

Question

https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

I have two table

[dbo].[table_product] and [dbo].[ex_table_product], and would like to merge [dbo].[ex_table_product] data into [dbo].[table_product] . Using the following code

DECLARE @SummaryOfChanges TABLE (Change VARCHAR(20));

MERGE INTO [dbo].[table_product] AS Target
USING (select ProductKey,ProductLabel,ProductName from [dbo].[ex_table_product])
AS Source (ProductKey,ProductLabel,ProductName)
ON (Target.ProductKey = Source.ProductKey)
WHEN MATCHED THEN
UPDATE SET ProductLabel = Source.ProductLabel
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductKey,ProductLabel,ProductName) VALUES (ProductKey,ProductLabel,ProductName)
OUTPUT $action INTO @SummaryOfChanges;

just think that if MERGE INTO already supported in Azure Synapse , as well as Azure synapse studio . cause there are so many syntax warning right there .

Answer 1

Instead of creating table variable @SummaryOfChanges, try creating temp table #SummaryOfChanges and insert data to it

Looks like table variables are not supported on Azure synapse

https://feedback.azure.com/forums/307516-azure-synapse-analytics/suggestions/13875030-support-table-variables

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

Hi sakuraime,

According to the documentation you cannot get output from a merge statement in Synapse. So I suspect removing the output portion of your query will solve the syntax error.

https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=azuresqldb-current

Answer 3

Same issue here. Should I enable the preview features somewhere or is the merge statement not supported in some regions atm? (mine is located in Western Europe)

Answer 4

I am also facing same issue, I don't know why giant Microsoft could not solve this issue, very sad :(