--- [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 10/16/2003 08:54 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).