Re: Bulk Inserting

andrewst <member14183_at_dbforums.com> wrote in message news:<3015186.1055928472_at_dbforums.com>...
> Originally posted by Adam C
> > Hello All
> >
> > I am struggling with a procedure that will do a batch/bulk insert of
> > parent child data. I have been tinkering with the following (sorry
> > about wrapping):
> >
> > DECLARE
> > type myt1 is table of subcriterion.CRITERIONID%type;
> > type myt2 is table of subcriterion.SUBCRITERIONID%type;
> > c myt1;
> > sc myt2;
> > newsc myt2;
> > BEGIN
> > select subcriterionid, criterionid bulk collect into sc, c from
> > subcriterion;
> >
> > forall i IN sc.first..sc.last
> > INSERT INTO subcriterion (CriterionID,
> > SubCriterionNumber,
> > SubCriterionName, SubCriterionDescription, SubCriterionCaveats,
> > Active)
> > SELECT c(i), SubCriterionNumber,
> > SubCriterionName,
> > SubCriterionDescription, SubCriterionCaveats, Active
> > FROM subcriterion where SubCriterionID = sc(i)
> > RETURNING SubCriterionID BULK COLLECT INTO
> > newsc;
> >
> > dbms_output.PUT_LINE (newsc.count);
> > END;
> >
> > I am getting an error saying "SQL command not properly ended". From
> > what I can
> > tell this is because I have tried to use the SELECT statement in the
> > FORALL
> > block (read somewhere that this isn't allowed I think).
> >
> > Can anyone suggest a fix or better approach to this situation?
> >
> > (Platform Win2k and Oracle8i)
> >
> > Thanks
> >
> > Adam C
> Unless I am mistaken, your code is attempting to do this (which doesn't
> need FORALL):
>
> BEGIN
> INSERT INTO subcriterion (CriterionID, SubCriterionNumber,
> SubCriterionName, SubCriterionDescription, SubCriterionCaveats,
> Active)
> SELECT CriterionID, SubCriterionNumber, SubCriterionName,
> SubCriterionDescription, SubCriterionCaveats, Active
> FROM subcriterion;
> dbms_output.PUT_LINE (SQL%ROWCOUNT);
> END;
>
> i.e. it duplicates every row in the subcriterion table and then writes
> out how many rows were inserted. Is that what you want?

Hi Andrew

Basically I have a series of tables
parent/{child|parent}/{child|parent} where each child record is potentially a parent record for another related table. I am trying to create a procedure that will create duplicate entries of existing entries within tables and at the same time exchange old parent keys for new parent keys.

Very nearly a cascade update scenario but not quite.

Thanks very much for the input.

Adam Received on Wed Jun 18 2003 - 19:41:24 CDT