Re: insert and commit 1000 records at a time

From: MaryAnn Atkinson <maryann_30_at_yahoo.com>
Date: Thu, 16 Oct 2003 12:49:24 -0800
Message-ID: <F001.005D367F.20031016124924@fatcity.com>

• Jared.Still_at_radisys.com wrote:
  > That will work, slowly.
  > You might like to try something like this
  > insert into resource
  > nologging
  > select * from rqmt
  > append;
  

How's that commiting every 1000 records?

> Read up on the 'append' and 'nologging' first.

???

>
>
>
>
>
>
>
> Maryann Atkinson <maryann_30_at_yahoo.com>
> Sent by: ml-errors_at_fatcity.com
> 10/16/2003 08:54 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: insert and commit 1000 records at a time
>
>
> I have 2 tables, Rqmt and Resource, same structure.
>
> I need to take all almost-one-million records from Rqmt and
> insert them to Resource. So far this worked ok:
>
> DECLARE
> RowCount NUMBER := 0;
>
> BEGIN
> SELECT Count(*)
> INTO RowCount
> FROM RQMT;
>
> IF RowCount > 0 THEN
>
> INSERT INTO RESOURCE
> SELECT Resource_Id, Classification
> FROM RQMT;
>
> RowCount := SQL%RowCount;
>
> DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
> Rows
> transitioned.');
> COMMIT;
> ELSE
> DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
> transitioned.');
> END IF;
>
> EXCEPTION
> WHEN OTHERS THEN
> Raise;
> END;
> /
>
>
> But now I need to commit every 1000 records. Any suggestions as to
> what would be the best way? I dont think ROWNUM would help here,
> because it would pick the same 1000 records every time, causing
> primary key violation...
>
>
> thx
> maa
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Maryann Atkinson
> INET: maryann_30_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>

---

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: MaryAnn Atkinson
  INET: maryann_30_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Oct 16 2003 - 15:49:24 CDT