Merge Statement - Capturing Insert, Update & Delete Counts

Question

Post reply

Merge Statement - Capturing Insert, Update & Delete Counts

Welsh Corgi

SSC Guru

Points: 116520
More actions
April 6, 2012 at 5:59 am

#253740

With a Merge Statement is there a way to Capture the Insert, Update & Delete Counts?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I see that you use the OUTPUT CLAUSE.

I'm still looking for a good example?

This is going to be encapsulated in an SSIS Package.

Also, I was just notified that they want $ Amounts on the incremental load someone made a decision to keep the initial load data in the staging table and perform a Merge between the initial Load and the incremental?

I ask why the did he not include an Insert_DateTime and I was told it is not needed.

The Business wants the $ Amounts for the incremental but I do not see how I can do that without Adding the Column and including it as a Variable in the Package Insert_DateTime Variable = GetDate() at the beginning of the package.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I don't know that it matters as far as syntax but I'm using an OPENQUERY?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I have a Stored Procedure that contains a Merge with an OPENQUERY Statement.

I plan on executing this Stored PRocedure in an EXEC SQL Task in an SSIS Package.

I need to capture the results of the MERGE OUTPUT INSERT, UPDATE & DELETE and store this is a table.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

The following examples are close but as far as I determine they do not cover update in the OUTPUT CLAUSE:

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

http://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ben Teraberry SSChampion Points: 11845 More actions · Answer 5

I may be misunderstanding what you're asking, but in the 2nd article you link he gives a nice example of what will be returned by $action in different situations in the OUTPUT clause. The action will be 'INSERT', 'UPDATE' or 'DELETE'.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

bteraberry (4/6/2012)
I may be misunderstanding what you're asking, but in the 2nd article you link he gives a nice example of what will be returned by $action in different situations in the OUTPUT clause. The action will be 'INSERT', 'UPDATE' or 'DELETE'.

Do you have a simple example? Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

I reposted and made the question very basic.

Please do not reply to this thread but to the following:

http://www.sqlservercentral.com/Forums/Post.aspx?SessionID=lkwvksiaegjxrk55d0ryiq45

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/