Home » SQL & PL/SQL » SQL & PL/SQL » inserting from one table to another (merged)
inserting from one table to another (merged) [message #335366] Tue, 22 July 2008 00:00 Go to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi,
I've the following requirement.
I created 2 tables
SQL> create table em(a number);

Table created.

SQL> create table em1(a number);

Table created.

Now i need to insert the data into em1 from table em providing if any duplicates are found it should warn us saying that duplicate value, otherwise it should insert into em1.

I've written the following pl/sql program for that. But it's giving wrong results.Could you please hint me the mistake in my program.
 DECLARE
  CURSOR C1 IS 
    SELECT *
    FROM   EM;
   CURSOR C2 IS 
     SELECT *
     FROM   EM1;
BEGIN
  FOR I IN C1 LOOP
    FOR J IN C2 LOOP
      IF I.A = J.A THEN
        DBMS_OUTPUT.PUT_LINE('Duplicate Value');
      ELSE
        INSERT INTO EM1
        VALUES     (I.A);
      END IF;
    END LOOP;
  END LOOP;
END;



Thank you
Re: inserting from one table to another [message #335367 is a reply to message #335366] Tue, 22 July 2008 00:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>But it's giving wrong results.

My car is giving wrong results.
Tell me how to make my car go.
Re: inserting from one table to another [message #335374 is a reply to message #335366] Tue, 22 July 2008 00:41 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
In em1 table there is no records . So after executing the program it should be filled with em table's data.
But i am getting the following result.
PL/SQL procedure successfully completed.

SQL> select count(*) from em1;

  COUNT(*)
----------
         0



So what would be the wrong in this program
Re: inserting from one table to another [message #335384 is a reply to message #335366] Tue, 22 July 2008 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create a unique constraint and use an "insert select" with exception/log errors clause.

Regards
Michel

[Updated on: Tue, 22 July 2008 01:15]

Report message to a moderator

Re: inserting from one table to another [message #335397 is a reply to message #335366] Tue, 22 July 2008 01:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Apart from choosing the wrong approach (see Michel's reply for the correct way to do it), you get zero records, because the actual insert is nested within the cursor-loop for table EM.
Table EM has no records, which means that the cursor-loop is not iterated. No code within that loop is ever executed.

You should have written something like this
declare
  cursor c_em
  is
    select *
    from   em;
  cursor c_em1
  (b_pk_column in em1.pk_column%type)
  is
    select *
    from   em1
    where  em1.pk_column = b_pk_column;
  r_em1 c_em1%rowtype;
begin
  for r_em in c_em
  loop
    begin
      open c_em1;
      fetch c_em1 into r_em1;
      -- If execution gets to here, it's a dupe
      close c_em1;
      dbms_output.put_line('We have a dupe: '||r_em1.pk_column);
    exception
      when no_data_found
      then
        close c_em1;
        -- Record not found in EM: insert
        insert into EM.....
    end;
  end loop;
end;


But, as you can see this is far more work than using an exception-into clause.
reg:performance issues [message #335525 is a reply to message #335366] Tue, 22 July 2008 07:49 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi,
The requirement is:

Millions of records should be inserted from one table to another table (duplicate records should be inserted in another table).

For that we can write a stored program but with as much as less time the operation should be done.

Please send me link where i can get the necessary information and can apply in my program to make it faster.

Thanks
Re: reg:performance issues [message #335532 is a reply to message #335525] Tue, 22 July 2008 08:23 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please send me link where i can get the necessary information and can apply in my program to make it faster.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2163698

Regards
Michel
Previous Topic: Spool from SP
Next Topic: procedure to backup table
Goto Forum:
  


Current Time: Sun Dec 11 00:17:55 CST 2016

Total time taken to generate the page: 0.07839 seconds