Re: INSERT millions_of_rows, how to COMMIT every 10,000 rows ?

From: Mike <nospam.thanks_at_nowhere.com>
Date: Mon, 28 Mar 2005 02:25:03 GMT
Message-ID: <3WJ1e.14924$C7.13308@news-server.bigpond.net.au>

> I want to insert millions of rows from old table into new table.
> (different in structure, so that I have manipulate in the query)
>
> INSERT INTO [new table] SELECT ... FROM [old table]
>
> How can I do COMMIT for every 10,000 rows ?
>
> Thank you for your help,
> Krist
>

Hi Krist

You could use a PL/SQL block like the following -

declare
  cursor oldtab_csr is select * from oldtab;   rec_count number := 1;
begin
  for oldtab_rec in oldtab_csr loop

---

    begin
      insert into newtab values (oldtab_rec.col1,oldtab_rec.col2,...);     exception
    when others then
      dbms_output.put_line('Unable to insert record '||oldtab_rec.pkcol||' - error encountered: '||sqlerrm);

    end;

---

    rec_count := rec_count + 1;
    if mod(rec_count,10000) = 0 then
      commit;
    end if;
  end loop;
end;

If you are able to add a column to your source table, you could have a flag to indicate that the
record was successfully transferred, which you set with an update statement immediately after
the insert - then in your source cursor exclude records that have already been transferred,
so that you are able to restart the script at any point.

If you are merging the data where many records already exist in the destination table, change the insert statement above as follows:

---

  update newtab

     set col1 = oldtab_rec.col1
       , col2 = oldtab_rec.col2

   where newtab.pkcol = oldtab_rec.pk_col;

  if sql%rowcount = 0 then
    insert into newtab values (oldtab_rec.col1,oldtab_rec.col2,...);   end if;

---

Alternatively if you don't have many records in newtab, then it may be more performant to strucure it with the insert first as follows:

---

  insert into newtab
  select oldtab_rec.col1,oldtab_rec.col2,...     from dual
   where not exists

         (select NULL
            from newtab
           where newtab.pkcol = oldtab_rec.pk_col);

  if sql%rowcount = 0 then
    update newtab

       set col1 = oldtab_rec.col1
         , col2=oldtab_rec.col2
     where newtab.pkcol = oldtab_rec.pk_col;

  end if;

---

Good luck!

Mike Cretan, Senior Oracle Developer Received on Sun Mar 27 2005 - 20:25:03 CST