CODEWITHSUNDEEP
sqloracle-databasecount
Prabu M
Prabu M

Reputation: 31

Row count after insert query and row count of select query

I am inserting records into the table and values are taken by using select query.

insert into my_table (....) 
select a.name, b.age, c.id, d.address 
from table1 a, table2 b, table3 c, table4 d 
where a.age=23 and d.addredd like '%street%';

I would like to compare the rowcount of select query and rowcount of my_table (after insertion).

how to take rowcount of both without taking more processing time.

Thanks

Upvotes: 3

Views: 16340

Answers (2)

Aramillo
Aramillo

Reputation: 3216

For get total of inserted rows you can use SQL%ROWCOUNT.

DECLARE

BEGIN
insert into my_table (....) 
select a.name, b.age, c.id, d.address 
from table1 a, table2 b, table3 c, table4 d 
where a.age=23 and d.addredd like '%street%';

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;

Edit:

Other way is create a function that return that value:

CREATE OR REPLACE FUNCTION get_count_inserted
   RETURN NUMBER
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   RESULT   NUMBER;
BEGIN
   insert into my_table (....)  -- Your query
   select a.name, b.age, c.id, d.address 
   from table1 a, table2 b, table3 c, table4 d 
   where a.age=23 and d.addredd like '%street%';

   RESULT := SQL%ROWCOUNT; --getting the count

   COMMIT;

   RETURN RESULT; --returning result
END;

Once function is created you can query it like this:

SELECT get_count_inserted FROM DUAL; --this will return total of rows inserted

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

how to take rowcount of both without taking more processing time.

The time to take the COUNT before and after the INSERT would be less than the time you took to post the question :-)

So, don't make things complicated, simply query for COUNT of ROWS before and after the INSERT into the table. No need to count the rows for select since the difference of number of rows before and after insert would be same as that of the select. If you still want to verify, you could take the count of select and check the difference, it would turn out to be same.

Unnecessarily introducing PL/SQL just to use SQL℅ROWCOUNT is not a good idea. Plain insert should be done in plain SQL.

Upvotes: 0

Related Questions