OUTPUT clause is not working with MERGE statement in Synapse

Shreyansh Jarged 0 Reputation points
2023-03-13T10:54:56.0733333+00:00

Hi All,

I am trying to Implement SCD Type-2 in Synapse Analytics - Dedicated SQL Pool

Code Skeleton :

MERGE <Target Table> AS TGT
USING <Source Table> AS SRC

WHEN MATCHED AND <Some Conditions>

THEN 
   UPDATE 
		 SET <Few Columns>


WHEN NOT MATCHED BY TARGET THEN 

INSERT ( Columns ) VALUES (SRC.COLUMNS)


OUTPUT $action

Below is the exact error statement :

Parse error at line: 200, column: 1: Incorrect syntax near 'OUTPUT'.

Can someone please help me understand, if OUPUT still is incompatible with MERGE ?

Thanks,

Shreyansh

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,557 questions
{count} votes

1 answer

Sort by: Most helpful
  1. BhargavaGunnam-MSFT 28,111 Reputation points Microsoft Employee
    2023-03-13T18:30:04.0966667+00:00

    Hello @Shreyansh Jarged,

    Welcome to the MS Q&A platform.

    The output clause is not currently supported in Synapse.

    Output clause currently applies to on-prem SQL, Azure SQL DB, and Azure SQL managed instances.

    User's image

    I would recommend submitting feature request feedback for the output clause in the synapse IDEAS forum: https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c

    All the feedback shared in this forum is actively monitored and reviewed by the Synapse engineering team, who will prioritize them based on the number of requests.

    Also, please share the link once the feedback is posted so that we can pass the feedback to respective product owners for further review.

    I hope this info helps.

    Document:

    https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16

    If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.