Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-04-11
5
Medium Priority
?
3,173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 1000 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 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

722 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