This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Obtaining rowcounts when using Composable DML [T-SQL]

In my August 2009 blog post Exploring Composable DML I introduced a new feature in SQL Server 2008 called Composable DML and also outlined one of its limitations; namely that data from the OUTPUT cannot be aggregated prior to insertion. Composable DML does have some useful scenarios however and one of those is in capturing and storing values that are replaced by an UPDATE (which I have talked about before in Using Composable DML to maintain entity history). Here’s the basic premise:

INSERT old_values( id, name )--use Composable DML to store the values that were replaced by an UPDATESELECT mrgout.deleted_id
, mrgout.deleted_name
FROM (MERGE tgt
USING src
ON tgt.id = src.id
WHENMATCHEDTHENUPDATESET tgt.NAME = src.NAME WHENNOTMATCHEDTHENINSERT(id,name)VALUES(src.id,src.name)OUTPUT$ACTIONAS action_
, INSERTED.id AS inserted_id
, INSERTED.NAME AS inserted_name
, DELETED.id AS deleted_id
, DELETED.name AS deleted_name
)mrgout
WHERE mrgout.action_ ='UPDATE'--Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table;

This statement updates some rows in [tgt] and stores the old values in [old_values]. I think that’s rather useful, especially in a data warehousing scenario where one may wish to MERGE to a type 1 dimension table. Unfortunately this scenario gives rise to another limitation of Composable DML – the value returned by @@ROWCOUNT is the number of rows that were affected in [old_values], not in [tgt]. The following code (which you can simply copy/paste and execute and which is also available on pastebin) demonstrates this problem:

/******************************************************************************************************************************A demonstration of capturing rowcounts when using composable DML. The problem I'm trying to demonstrate here is that I don't think there is a way to capture the number of rows affected by the MERGEJamie Thomson, 2013-02-07******************************************************************************************************************************//*Setup table first and insert some data into [src]*/USE tempdb
IFOBJECT_ID('src')ISNOTNULLDROPTABLE src;CREATETABLE src(
id INT, name NVARCHAR(MAX));IFOBJECT_ID('tgt')ISNOTNULLDROPTABLE tgt;CREATETABLE tgt(
id INT, name NVARCHAR(MAX));/*[updates] will be used as the target of the Composable DML insertion*/IFOBJECT_ID('old_values')ISNOTNULLDROPTABLE old_values;CREATETABLE old_values(
id INT, name NVARCHAR(MAX));INSERT src(id,name)VALUES(1,'don'),(2,'kaina');GO/*Everything after here gets run twice because the batch ends with GO 2*/INSERT old_values( id, name )--use Composable DML to store the values that were replaced by an UPDATESELECT mrgout.deleted_id
, mrgout.deleted_name
FROM (MERGE tgt
USING src
ON tgt.id = src.id
WHENMATCHEDTHENUPDATESET tgt.NAME = src.NAME WHENNOTMATCHEDTHENINSERT(id,name)VALUES(src.id,src.name)OUTPUT$ACTIONAS action_
, INSERTED.id AS inserted_id
, INSERTED.NAME AS inserted_name
, DELETED.id AS deleted_id
, DELETED.name AS deleted_name
)mrgout
WHERE mrgout.action_ ='UPDATE'--Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table;SELECT [@@ROWCOUNT]=@@ROWCOUNT,row_tally_in_tgt=(SELECTCOUNT(*)FROM tgt)-- <-Rowcount only provides tally of rows affected by the outer INSERT, not the MERGEGO 2

Here is the output:

Notice that the Composable DML containing the MERGE statement is executed twice. The first execution inserts two rows into [tgt] yet @@ROWCOUNT returns zero because zero rows were inserted into [old_values] by the outer query. The second execution results in two rows in [tgt] being updated hence two rows are inserted into [old_values] and hence why @@ROWCOUNT returns two. It appears there is no way to discover the number of inserts or updates that were committed by the MERGE; if you’re a fan of logging rowcounts during ETL operations (which I think you should be) then this is a big problem. The only way I can think of getting around this problem is to break the statement into two like so (for brevity I haven’t included the full code listing so it is also available on pastebin):

This is much better. We now know the tally of insertions and updates committed by the MERGE, unfortunately we have had to do it in two separate statements which in a way defeats the point of using MERGE in the first place (and don’t forget some of the other current problems with MERGE). If you can think of a better way of doing it then I’m all ears – please reply in the comments below.

I’m not saying don’t use MERGE and I’m not saying don’t use Composable DML; just be aware of their limitations. Personally I think there should be built-in functions, similar to @@ROWCOUNT, that return the number of rows INSERTed/DELETEd/UPDATEd by a MERGE; Aaron Bertrand agreed and raised a Connect submission to that affect: Katmai : Merge does not distinguish rowcounts in triggers which has, unfortunately, “been closed as won’t fix”.

Hi Jamie, nice article and agree with you on MERGE needing to return @@ROWCOUNT for every operation, nevertheless, in my case, I usually have control columns (batchid, lastoperationtype) which easilly allow me to infer what was inserted/updated for a given batch and do appropriate counting/control.