MERGE INTO Azure synapse

sakuraime 2,316 Reputation points
2020-11-27T06:41:01.823+00:00

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 .

43164-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,346 questions
{count} vote

4 answers

Sort by: Most helpful
  1. Vaibhav Chaudhari 38,566 Reputation points
    2020-11-27T07:01:25.88+00:00

    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

    1 person found this answer helpful.

  2. Daniel Goucher 1 Reputation point
    2020-11-27T10:49:43.23+00:00

    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


  3. Dries Canfyn 1 Reputation point
    2021-01-04T11:00:01.46+00:00

    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)

    53227-screenshot-1.png

    0 comments No comments

  4. Sunil P 136 Reputation points
    2021-08-23T04:28:16.667+00:00

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