CODEWITHSUNDEEP
sqloracle-database
user3074636
user3074636

Reputation: 19

How many rows are inserted

I just want to know how many rows are inserted in a table by using SQL only.

For Example: consider 2 tables TT and TT1 and I want to insert into TT1 using TT table like this:

INSERT INTO TT1 
SELECT * 
FROM TT 
WHERE 1=1;

after executing above statement I want to know how many rows are inserted? by using Oracle SQL only. Is there any way?

Upvotes: 2

Views: 333

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

INSERT INTO TT1 
SELECT * 
FROM TT 
WHERE 1=1;

The number of rows inserted are the number of rows returned by the SELECT statement. Which is nothing but the count of all the rows of table TT, since 1=1 is always TRUE, basically filtering nothing.

So, just do:

SELECT COUNT(*)
FROM TT;

Update Added an example

For example,

Let's say I have a table T which is empty:

SQL> create table t as select * from emp;

Table created.

I insert rows from EMP table to T:

SQL> insert into t select * from emp;

14 rows created.

I want to know how many rows were inserted, which is nothing but the rows returned by the SELECT statement.

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

This is in pure SQL. i.e. you executed an insert, and now want to know how many rows actually inserted by the INSERT INTO..SELECT.

However, when you have a lot of insert statements happening programmatically i.e. in PL/SQL, then you will need SQL%ROWCOUNT for every INSERT.

Upvotes: 1

Hüseyin Zengin
Hüseyin Zengin

Reputation: 1226

If you are able to use PL/SQL you can do it like

declare
  i number;
begin
  INSERT INTO TT1 SELECT * FROM TT WHERE 1=1;
  i := sql%rowcount;
end;

Otherwise as far as I know there is only way to do it is using SELECT COUNT, if there are records before this query just run SELECT COUNT twice, one before executing and one after query then just substract them.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

The PL/SQL expression SQL%ROWCOUNT may do what you want. You would need to phrase this as:

BEGIN
    INSERT INTO TT1 
    SELECT * 
    FROM TT 
    WHERE 1=1;

    DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT)
END;

In other words, it needs to go into a PL/SQL code block.

Upvotes: 3

Related Questions