Getting the row count after a merge statement

Question

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

Answer 1

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';