Obtaining rowcounts when using Composable DML [T-SQL]

SSIS Junkie : Obtaining rowcounts when using Composable DML

SSIS Junkie : Obtaining rowcounts when using Composable DML — Thu, 07 Feb 2013 14:03:14 GMT

PingBack from http://sqlblog.com/blogs/jamie_thomson/archive/2013/02/07/obtaining-rowcounts-when-using-composable-dml.aspx

re: Obtaining rowcounts when using Composable DML [T-SQL]

Adam Machanic — Thu, 07 Feb 2013 16:00:04 GMT

Hm, I read "mrgout," rather than "Merge Out," as "Mr. Gout." Maybe not the best name in the world :-)

re: Obtaining rowcounts when using Composable DML [T-SQL]

jbooker — Thu, 07 Feb 2013 16:08:52 GMT

@@ROWCOUNT limitation in the context of OUTPUT using DML is listed here:

http://msdn.microsoft.com/en-us/library/ms177564.aspx

re: Obtaining rowcounts when using Composable DML [T-SQL]

Fernando Marçal — Thu, 07 Feb 2013 23:30:35 GMT

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.

re: Obtaining rowcounts when using Composable DML [T-SQL]

jamiet — Fri, 08 Feb 2013 21:32:16 GMT

Fernando,

I agree, and I have the same thing - I refer to those columns as data lineage. Perhaps another blog post is required.

Josh, thanks for that. For those that can't be bothered going to find it, thee article that Josh (jbooker) linked toreads:

"•@@ROWCOUNT returns the rows inserted only by the outer INSERT statement."

Machanic - there are no words :)

Regards

Bootstrapping SQL Server bloggers and blog readers with Twitter!

SSIS Junkie — Sun, 03 Mar 2013 23:21:10 GMT

On 17th December 2009 Aaron Nelson (you may know him as @sqlvariant ) had a great idea – he invented