Is it possible to count Number of record inserted and Number of Record Updated in "Merge" Command?
If yes Please give me an example?
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).