1. Define a variable to count inserted rows.
insert_count number := 0;
2. Increment it after inserting the row
insert_count:=insert_count + 1;
3. Check if insert_count = 1000
then commit and reset counter to zero insert_count :=0;
4. At the end when no rows found, and insert_count > 0 then commit.
I think you get the idea....
- Kirti
--- MaryAnn Atkinson <[EMAIL PROTECTED]> wrote:
> I still dont get it...
> I dont know what I have done to have me confused more
> than I first asked the question...
>
>
> --- [EMAIL PROTECTED] wrote:
> > My bad. The SQL is not quite right: 'append' is a hint:
> > alter table resource nologging;
>
> dont know what nologging does.
>
>
> > insert /*+ append */ into resource
> > select * from rqmt;
>
> me no understand... me no see 1000 anywhere...
>
> > Read up on direct load insert in the concepts manual,
> > along with nologging.
>
> And one more thing... If I asked the question, thats just it,
> I asked a question. If anyone knows the answer, please offer it here,
> but dont tell me to go read it up in the national enquirer or
> I-dont-know-where-you-mean...
>
> Folks, please, if we have something to offer, lets go ahead,
> if not, just bypass that email and read another one...
>
> thanks,
> maa
>
>
>
>
> >
> > Bypass the redo and undo - no need for commits.
> >
> > Just back it up when finished.
> >
> > Jared
> >
> >
> >
> >
> >
> >
> >
> > MaryAnn Atkinson <[EMAIL PROTECTED]>
> > Sent by: [EMAIL PROTECTED]
> > 10/16/2003 01:49 PM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <[EMAIL PROTECTED]>
> > cc:
> > Subject: Re: insert and commit 1000 records at a time
> >
> >
> >
> > --- [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
__________________________________
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: Kirtikumar Deshpande
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).
