Getting the row count after a merge statement

pmscorca 512 Reputation points
2024-01-29T18:24:21.9933333+00:00

Hi, in Synapse I need to implement a MERGE statement and then I'd like to get the row count respect to processed rows. It seems that Synapse doesn't support @@ROWCOUNT and so I need to think an alternative solution. I've found the possibility of use OPTION (LABEL = '...') after an INSERT, UPDATE, MERGE statement and then querying the sys.dm_pdw_request_steps and sys.dm_pdw_exec_requests system views. It is right or does it exist another better solution? Thanks

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,140 questions
0 comments No comments
{count} votes

Accepted answer
  1. Amira Bedhiafi 12,481 Reputation points
    2024-01-30T09:38:15.4433333+00:00

    In AS, if @@ROWCOUNT is not supported for your use case, using the OPTION (LABEL = '...') method combined with querying sys.dm_pdw_request_steps and sys.dm_pdw_exec_requests system views can be an alternative to get the row count after executing a MERGE statement.

    -- Your MERGE statement
    MERGE INTO your_table
    USING source_table
    ON your_table.key = source_table.key
    WHEN MATCHED THEN
        UPDATE SET your_table.value = source_table.value
    WHEN NOT MATCHED THEN
        INSERT (key, value)
        VALUES (source_table.key, source_table.value)
    OPTION (LABEL = 'YourMergeQueryLabel');
    -- Query to get row count
    SELECT rs.row_count
    FROM sys.dm_pdw_request_steps rs
    JOIN sys.dm_pdw_exec_requests er ON rs.request_id = er.request_id
    WHERE er.[label] = 'YourMergeQueryLabel';
    

0 additional answers

Sort by: Most helpful