Home » SQL & PL/SQL » SQL & PL/SQL » merge - how to idenitfy the number of rows inserted and updated (OS=AIX5L, Oracle Database 10g)
merge - how to idenitfy the number of rows inserted and updated [message #290781] Mon, 31 December 2007 14:33 Go to next message
smithsl
Messages: 9
Registered: August 2007
Junior Member
Upon completion of a sql MERGE statement, I would like to know the number of rows inserted and the number of rows updated. Any suggestion on how to achieve this.
Re: merge - how to idenitfy the number of rows inserted and updated [message #290784 is a reply to message #290781] Mon, 31 December 2007 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no way.
(Some posted some ways in other forums but they don't work in concurrent environment or are performances killers).

In addition, in 10g MERGE can also delete, do you also want the number of deleted rows?

Regards
Michel
Re: merge - how to idenitfy the number of rows inserted and updated [message #290788 is a reply to message #290784] Mon, 31 December 2007 15:03 Go to previous messageGo to next message
smithsl
Messages: 9
Registered: August 2007
Junior Member
Sure let's include the delete count as well to make the thread all inclusive.
Thanks for your response.
Oracle currently gives the total number of rows merged. Perhaps in a future version, they will break that down further into the number of inserts, updates and deletes.
Re: merge - how to idenitfy the number of rows inserted and updated [message #290799 is a reply to message #290788] Mon, 31 December 2007 19:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The power of MERGE is that it doesn't NEED to separate inserts from updates. It's an annoyance that it does not report the numbers separately, because it does actually track them internally (for determining when statistics are stale - see ALL_TAB_MODIFICATIONS). But that's all it is: an annoyance.

You need to carefully examine your reason for wanting to know. Usually it is for the collection of runtime metadata set up on a pre-9i system. Collecting metadata is important for reconciliation and debugging, but we shouldn't get too hung up about it.

So now you have a decision: is it better to comply with the archaic metadata collection standard, or to exploit a new and powerful tool.

Ross Leishman
Re: merge - how to idenitfy the number of rows inserted and updated [message #292046 is a reply to message #290781] Mon, 07 January 2008 10:18 Go to previous messageGo to next message
smithsl
Messages: 9
Registered: August 2007
Junior Member
I like your thinking, please continue to develop my thinking in this manner.

My reason for wanting the counts originates as below.
My task is to upsert data located in a data file, into a db table.
I chose to accomplish the task using an external table definition and the sql merge statement.
Along w/ my completed code, I must submit an Acceptance document.
For that document, I must derive a test case (to serve as a form of proof, I guess) which demonstrates I accomplished the task above.
I was looking to meet the documentation requirement by simply showing pre/post counts.
For example: Compare the expected inserts/update counts - to the actual insert/update counts. If they match, a bit of faith in the merge is achieved.
To grab and compare a few records pre and post processing would serve as a test case as well, perhaps a better one even, but it requires more code than the counts and its value is about the same as the counts.
Thus my question on this website.
Re: merge - how to idenitfy the number of rows inserted and updated [message #292048 is a reply to message #292046] Mon, 07 January 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you trust more in your insert/update process than in the merge statement?

Regards
Michel
Re: merge - how to idenitfy the number of rows inserted and updated [message #292052 is a reply to message #292048] Mon, 07 January 2008 10:28 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Michel,

I don't think he trusts in it more, I think it is to produce a portion of the risk analysis/testing section(s) of the design document. To prove that the new program has inserted/updated/deleted the expected number of rows.

As the OP stated, he can do the same with some sample test cases, but it would be easier if the update/insert/delete numbers
are available.

Re: merge - how to idenitfy the number of rows inserted and updated [message #292058 is a reply to message #290781] Mon, 07 January 2008 10:43 Go to previous messageGo to next message
smithsl
Messages: 9
Registered: August 2007
Junior Member
If I created my own "insert/update process/statement", I would still have the same requirement to write a test case, just as I do for the merge statement.
It's the documentation required at my job, that's pointing me down this road. I believe a simple count would satisfy the document requirement.
A "count output" from the merge statement would be an easy test case (proof I accomplished what they asked me to do) to put in the acceptance document.

It is not that I trust one method over the other. I trust that if the merge condition I specified is met, oracle will update, otherwise it will insert.

The response time is amazing on this website. Thank you.
Re: merge - how to idenitfy the number of rows inserted and updated [message #292060 is a reply to message #292052] Mon, 07 January 2008 10:45 Go to previous messageGo to next message
smithsl
Messages: 9
Registered: August 2007
Junior Member
Simply and well stated! That is where I stand. Thank you.
Re: merge - how to idenitfy the number of rows inserted and updated [message #292061 is a reply to message #292058] Mon, 07 January 2008 10:51 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I meant is: if the counts are different, what do you conclude?

Regards
Michel
Previous Topic: Using WITH clause with DML Statements
Next Topic: Any suggestions
Goto Forum:
  


Current Time: Wed Dec 07 05:17:50 CST 2016

Total time taken to generate the page: 0.06647 seconds