/******************************************************************************************************************************
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 MERGE
Jamie Thomson, 2013-02-07
******************************************************************************************************************************/
/*Setup table first and insert some data into [src]*/
USE tempdb
IF OBJECT_ID('src') IS NOT NULL DROP TABLE src;
CREATE TABLE src (
id INT
, name NVARCHAR(MAX)
);
IF OBJECT_ID('tgt') IS NOT NULL DROP TABLE tgt;
CREATE TABLE tgt (
id INT
, name NVARCHAR(MAX)
);
/*[updates] will be used as the target of the Composable DML insertion*/
IF OBJECT_ID('old_values') IS NOT NULL DROP TABLE old_values;
CREATE TABLE 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 )
SELECT mrgout.deleted_id
, mrgout.deleted_name
FROM (
MERGE tgt
USING src
ON tgt.id = src.id
WHEN MATCHED THEN
UPDATE
SET tgt.NAME = src.NAME
WHEN NOT MATCHED THEN
INSERT(id,name)
VALUES(src.id,src.name)
OUTPUT $ACTION AS 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=(SELECT COUNT(*) FROM tgt) -- <-Rowcount only provides tally of rows affected by the outer INSERT, not the MERGE
GO 2