Solved

PL/SQL MERGE STATEMENT.  I need a count of both inserted and updated rows.

Posted on 2007-04-11
5
3,093 Views
Last Modified: 2009-03-10
I have the following merge statement that either inserts a new record OR updates a record.  Then it prints out the number of rows merged.

I want to be able to print out BOTH number of rows inserted and number of rows updated.  Is there a way to do this, and how would it be done?

MERGE INTO dm_table dm
      USING (SELECT *
               FROM mytable              WHERE status_ = c_load_stat_new) dmld
      ON (dm.id= dmld.dm_physician_id)
      WHEN MATCHED THEN
         UPDATE
            SET a =a1
      WHEN NOT MATCHED THEN
         INSERT
            (a)
         VALUES
            (a1);
      dbms_output.put_line(to_char(SQL%ROWCOUNT) || ' rows merged.');
      --
            COMMIT;
0
Comment
Question by:mintsii
5 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 18892202
Do before and after row counts so you can compute the difference.
0
 

Author Comment

by:mintsii
ID: 18892247
That was the one option I had considered, however, I was wondering if there was another way to handle it.  Perhaps there is a function like sql%rowcount that counts inserts or updates?
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 250 total points
ID: 18895454
I am not aware of a direct psuedo column that gives the insert and update breakup.
But, there are a couple of work arounds that you can use to get these counts.

The easiest one would be:
 - use the same join condition to get the counts as shown in this example.

declare
 l_upd_c number := 0;
 l_ins_c number := 0;

begin

 select count(*) into l_upd_c
 from emp2, (select * from emp where sal is not null) x
 where (emp2.ename = x.ename);

 select count(*) into l_ins_c
 from (select * from emp where sal is not null) x;

 l_ins_c := l_ins_c - l_upd_c;

 merge into emp2
 using (select * from emp where sal is not null) x
 on (emp2.ename = x.ename)
 when matched then
  update
  set sal = sal
 when not matched then
  insert
  values(x.ename, x.sal );

if sql%rowcount = l_upd_c + l_ins_c then
 dbms_output.put_line(l_upd_c);
 dbms_output.put_line(l_ins_c);
end if;

end;
/

A better(but more complex) way is to use a separate package to get the counts. See the following link:
http://www.revealnet.com/newsletter-v4/0903_D.htm
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now