Skip to Main Content
  • Questions
  • How to count the inserts and updates from merge

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question.

Asked: August 15, 2017 - 12:31 pm UTC

Last updated: August 15, 2017 - 2:32 pm UTC

Version: 11.0

Viewed 10K+ times! This question is

You Asked

Is it possible to count Number of record inserted and Number of Record Updated in "Merge" Command?
If yes Please give me an example?

and Chris said...

There isn't an in built way. SQL%ROWCOUNT tells you the total number inserted or updated, not the breakdown:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:122741200346595110

You can build a DIY solution though:

- Add a timestamp and insert/update flag to the target table
- Get the timestamp before running the merge and stuff it into a variable
- Set the table's timestamp using this variable and set the insert/update flag appropriately
- Query the table to find the rows just updated, grouped by the flag:

create table t (
  x int, y date, z varchar2(1)
);

var dt varchar2(20);

exec :dt := to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss');

insert into t values (1, null, null);

merge into t
using (select level l, :dt d from dual connect by level <= 2) d
on    (t.x = d.l)
when matched then update
  set y = to_date(:dt, 'yyyy/mm/dd hh24:mi:ss'), z = 'U'
when not matched then 
  insert (x, y, z)
  values (l, to_date(:dt, 'yyyy/mm/dd hh24:mi:ss'), 'I');
  
select z, count(*) from t
where  y = to_date(:dt, 'yyyy/mm/dd hh24:mi:ss')
group  by z;

Z  COUNT(*)  
U  1         
I  1    


If two people run this within the same second you could get false positives. So you'll probably want to use timestamps with lots of fractional seconds to avoid this. Or some other value you assign before running the merge (e.g. transaction id, sequence value or guid).

We're not taking comments currently, so please try again later if you want to add a comment.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.