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

Breadcrumb

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

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.