convert query from merge to select count in Oracle

Question

I have merge query with below syntax:

MERGE INTO target_table 
USING source_table 
ON search_condition
    WHEN NOT MATCHED THEN
        INSERT (col1,col2,...)
        values(value1,value2,...)
        WHERE <insert_condition>;

But i want change this query to see how many rows would insert and use the below query but i am not sure this is correct query:

select count(*) from target_table where not exists (select 1 from source_table where search_condition)

score 1 · Accepted Answer · answered Feb 12 '19 at 11:14

1

The MERGE statement inserts rows from source_table into target_table. So it is data from the source_table that doesn't yet exist in the target_table you want to count.

select count(*)
from source_table 
where <insert_condition>
and not exists
(
  select *
  from target_table
  where <search_condition>
);

answered Feb 12 '19 at 11:14

Thorsten Kettner

89,309
7
49
73

I tried above query but its not returning expected result. – Nitin Feb 12 '19 at 12:38
That is strange. So you know that the `MERGE` would insert, say, exactly 1000 rows, but my query doesn't return 1000? Have you tried it? (You can run the `MERGE` statement and then rollback.) – Thorsten Kettner Feb 12 '19 at 13:36
Yes i run parallel both query and getting the different result, First i run the query then merge and getting different result. – Nitin Feb 12 '19 at 13:47
1

Hm, strange. Sorry, I have no explanation for this. I guess you'll have to show the real statements, so we can see what's happening. – Thorsten Kettner Feb 12 '19 at 13:59
sorry it was my mistake above query is running same result. – Nitin Feb 12 '19 at 14:19

score 0 · Answer 2 · answered Feb 12 '19 at 11:07

0

You don't have to count number of inserted rows separately. If you ran it in SQL*Plus, it shows the number itself.

If you use that MERGE as a part of PL/SQL procedure, then you'd use SQL%ROWCOUNT:

declare
  l_cnt number;
begin
  merge into target_table
    using ...;

  l_cnt := SQL%ROWCOUNT;            --> this is what you want

  dbms_output.put_line('Inserted ' || l_cnt || ' rows');
end;

I stored it into a local variable so that it is possible to do something with it later (compare it to some other values, whatever).

answered Feb 12 '19 at 11:07

Littlefoot

131,892
15
35
57

Yes but i didnt want to use merge query because it will taking time so i want to use only select query. – Nitin Feb 12 '19 at 11:10
2

@Nitin: That it takes so much time, shouldn't be the reason. Either you want to insert data into `target_table` or you don't. If you want to do this use `MERGE`, if you merely want to see how many rows you *would* insert, then use a query. – Thorsten Kettner Feb 12 '19 at 11:17
Yes, I want to see how many rows would insert and tried the query which is given by you but result are different. – Nitin Feb 12 '19 at 12:40
Well, Oracle doesn't lie. If SQL%ROWCOUNT said that merge *merged* certain number of rows (which includes INSERT and UPDATE), then that's the number. – Littlefoot Feb 12 '19 at 12:59
You can make a savepoint before the MERGE statement, then compare the SQL%ROWCOUNT with the expected value and if the number of inserted rows is incorrect, then do rollback to savepoint. – alexeionin Feb 12 '19 at 17:37

convert query from merge to select count in Oracle

2 Answers2