Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /******************************************************************************************************************************
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement