SSIS Junkie : merge

Obtaining rowcounts when using Composable DML [T-SQL]

jamiet — Thu, 07 Feb 2013 13:59:57 GMT

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

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 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 ) --use Composable DML to store the values that were replaced by an UPDATE
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

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):

/*Setup part is the same as before, we do need an extra table for capturing the output of our MERGE tho*/
IF OBJECT_ID('mrgout') IS NOT NULL		DROP TABLE mrgout;
CREATE TABLE mrgout (
	action_			NVARCHAR(MAX)
,	inserted_id		INT
,	inserted_name    NVARCHAR(MAX)
,	deleted_id		INT
,	deleted_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*/
TRUNCATE TABLE mrgout;
INSERT mrgout(action_, inserted_id, inserted_name,deleted_id, deleted_name )
SELECT	mrgout.action_
,		mrgout.inserted_id
,		mrgout.inserted_name
,		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
;
INSERT	dbo.old_values(id,name)
SELECT	deleted_id,deleted_name FROM mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
SELECT	[INSERT_@@ROWCOUNT]=(SELECT COUNT(*) FROM mrgout WHERE action_ = 'INSERT'),[UPDATE_@@ROWCOUNT]=(SELECT COUNT(*) FROM mrgout WHERE action_ = 'UPDATE'),row_tally_in_tgt=(SELECT COUNT(*) FROM tgt)   GO 2

Executing that returns:

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”.

@Jamiet

The current state of a MERGE Destination for SSIS

jamiet — Fri, 07 Dec 2012 11:18:45 GMT

Hugo Tap asked me on Twitter earlier today whether or not there existed a SSIS Dataflow Destination component that enabled one to MERGE data into a table rather than INSERT it. Its a common request so I thought it might be useful to summarise the current state of play as regards a MERGE destination for SSIS.

Firstly, there is no MERGE destination component in the box; that is, when you install SSIS no MERGE Destination will be available. That being said the SSIS team have made available a MERGE destination component via Codeplex which you can get from http://sqlsrvintegrationsrv.codeplex.com/releases/view/19048.

I have never used it so cannot vouch for its usefulness although judging by some of the reviews you might not want to set your expectations too high. Your mileage may vary.

In the past it has occurred to me that a built-in way to provide MERGE from the SSIS pipeline would be highly valuable. I assume that this would have to be provided by the database into which you were merging hence in March 2010 I submitted the following two requests to Connect:

BULK MERGE (111 votes at the time of writing)
[SSIS] BULK MERGE Destination (15 votes)

If you think these would be useful feel free to vote them up and add a comment.

Lastly, this one is nothing to do with SSIS but if you want to perform a minimally logged MERGE using T-SQL Sunil Agarwal has explained how at Minimal logging and MERGE statement.

@Jamiet

MERGE and OUTPUT – the swiss army knife of T-SQL

jamiet — Wed, 06 Jan 2010 18:46:22 GMT

The new T-SQL MERGE statement in SQL Server 2008 seems to be finding many uses over and above its de facto UPSERT usage scenario, probably the most popular of which is its ability to update one table from another in an ANSI-compliant manner as detailed expertly by Hugo Kornelius in his blog post Let's deprecate UPDATE FROM!

Today I stumbled upon a different use for it, returning values using an OUTPUT clause from a table used as the source of data for an insertion. In other words, if I’m inserting from [tableA] into [tableB] then I may want some values from [tableA] after the fact, particularly if [tableB] has an identity. Observe my first attempt using a straight insertion where I am trying to get a field from @source.[id] that is not used in the insertion:

DECLARE @source TABLE (

   [id] INT PRIMARY KEY

,  [name] VARCHAR(10)

);

INSERT @source VALUES(1000,'Harold'),(2000,'Madge');

DECLARE @destination TABLE (

   [id] INT PRIMARY KEY IDENTITY(1,1)

,  NAME VARCHAR(10)

);



INSERT @destination ([name])

OUTPUT INSERTED.[id] AS NEWID,[source].[id] AS OldId

SELECT [name]

FROM   @source;

This failed with the error:

The reason this fails is that the INSERT statement has no FROM clause thus the OUTPUT clause can never know about where the data is sourced from; the FROM clause in the SELECT that is used to source the data for the insertion is not actually part of the INSERT statement. Let’s try this with a MERGE instead:

DECLARE @source TABLE (

   [id] INT PRIMARY KEY

,  [name] VARCHAR(10)

);

INSERT @source VALUES(1000,'Harold'),(2000,'Madge');

DECLARE @destination TABLE (

   [id] INT PRIMARY KEY IDENTITY(1,1)

,  NAME VARCHAR(10)

);



MERGE  @destination

USING  (SELECT [id], [name] FROM @source) AS [source]

ON     (1=0) --arbitrary join condition

WHEN   NOT MATCHED THEN

       INSERT (name)

       VALUES  (source.Name)

       OUTPUT  INSERTED.id AS NEWID,[source].[id] AS OldId,INSERTED.name;

This worked!

Notice how we are able to reference the source of our data in the OUTPUT clause of of our MERGE statement which we couldn’t do when using INSERT. Pretty cool huh!

I remembered while writing this that Adam Machanic has already documented this capability in his blog post Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE and he has covered it in a lot more detail than I have here but still, I see no harm in reiterating and the code above is an easily executable demo of this cool little feature.

@Jamiet

N.B. Thanks to Aaron Bertrand for his instructions here on producing well-formatted T-SQL code for blogs! I’m putting this comment here so I know where to find it next time!

Extracting insert, update, delete rowcounts from T-SQL MERGE

jamiet — Sat, 29 Aug 2009 23:54:16 GMT

Just lately I’ve been using T-SQL’s MERGE statement (introduced in SQL Server 2008) and one thing that I needed to do was extract rowcounts for each DML operation (i.e. INSERT, UPDATE, DELETE) conducted by a MERGE. I was surprised to find that while @@ROWCOUNT is supported for MERGE, it only returns the total number of affected rows and there are no built in functions for getting the counts for each DML operation (although Aaron Bertrand has a Connect submission asking for it).

There is an easy way around this however; MERGE includes a function called $action which can be used in the OUTPUT clause to specify whether a row was inserted, updated or deleted. I cut a small bit of code that demos how it works which you can get at the bottom of this blog post. Here’s what it produces:

I hope its useful to someone!

If you want to learn more about the MERGE statement and the OUTPUT clause then head over and read Adam Machanic’s post Dr OUTPUT: Or how I learned to stop worrying and love the MERGE.

@Jamiet

Here’s the code!! Disclaimer: Take it or leave it – just don’t shout at me if there are any problems here (although constructive comments are welcomed)

set nocount on;

create table #t
(
    id int
,    name varchar(max)
);
go

insert #t values (1,'Jim'),(2,'Sarah'),(3,'Hels');
go

DECLARE @rowcounts TABLE
(
    mergeAction nvarchar(10)
);
declare @insertCount int, @updateCount int, @deleteCount int;
merge into #t as tgt
using (    select 1 as id, 'James' as name
        union
        select 2, 'Sarah'
        union
        select 3, 'Helen'
        union
        select 4, 'Jack'
        union
        select 5, 'Annie') as src
on    tgt.id = src.id
when matched and tgt.name = src.name 
        THEN DELETE
when matched and tgt.name <> src.name
        THEN UPDATE SET tgt.name = src.name
when not matched
        THEN insert values (src.id, src.name)
OUTPUT $action into @rowcounts;

select    @insertcount=[INSERT]
,        @updatecount=[UPDATE]
,        @deletecount=[DELETE]
from    (
        select    mergeAction,1 rows
        from    @rowcounts
        )p
pivot
(
    count(rows)
FOR    mergeAction IN 
(    [INSERT], [UPDATE], [DELETE])
) as pvt
;
drop table #t;


print    '@insertcount = ' + cast(@insertcount as varchar);
print    '@updatecount = ' + cast(@updatecount as varchar);
print    '@deletecount = ' + cast(@deletecount as varchar);