Re: Bulk Inserting

From: Adam C <adam_at_ddisolutions.com.au>
Date: 24 Jun 2003 17:40:38 -0700
Message-ID: <8bdc35cd.0306241640.57347060@posting.google.com>

Just some closure on this,

In the end I finally was able to come up with the sql statements that did
the work for me, and I didn't need to use any RETURNING or BULK COLLECT
statements. Anyway here is a snip of the code I ended up settling on:

--Create copies of the current Criteria

                 INSERT INTO CRITERION (PERIODID, CRITERIONNUMBER, CRITERIONNAME, CRITERIONDESCRIPTION, CRITERIONCAVEATS, ACTIVE)                  SELECT m_ID, CRITERIONNUMBER, CRITERIONNAME, CRITERIONDESCRIPTION, CRITERIONCAVEATS, ACTIVE
                 FROM CRITERION WHERE CRITERION.PERIODID = PID AND CRITERION.ACTIVE = 1;

		 --CREATE COPIES OF CURRENT SUBCRITERIA DATA
		INSERT INTO SUBCRITERION (CRITERIONID, SUBCRITERIONNUMBER,

SUBCRITERIONNAME, SUBCRITERIONDESCRIPTION, SUBCRITERIONCAVEATS, ACTIVE)
                SELECT SC.CRITERIONID, SC.SUBCRITERIONNUMBER,SC.SUBCRITERIONNAME, SC.SUBCRITERIONDESCRIPTION, SC.SUBCRITERIONCAVEATS, 0

		FROM SUBCRITERION SC, CRITERION C
	   	WHERE ( (SC.CRITERIONID = C.CRITERIONID) AND (C.PERIODID = PID)

AND (SC.ACTIVE = 1));                 --UPDATE NEW RECORDS CRITERIONID (BY FILTERING FOR ACTIVE = 0) TO NEWLY DUPLICATED CRITERIONID

		UPDATE SUBCRITERION SC1
		SET (SC1.CRITERIONID, SC1.ACTIVE) = (SELECT C1.CRITERIONID, (1) TMP
											FROM CRITERION C1, CRITERION C2
											WHERE ( (C1.CRITERIONNAME = C2.CRITERIONNAME) AND


											(C1.PERIODID = m_ID) AND (C2.PERIODID = PID) AND

											(C2.CRITERIONID = SC1.CRITERIONID) )

											GROUP BY C1.CRITERIONID , C1.PERIODID )
		WHERE SC1.ACTIVE = 0;

As you can see I am doing an insert then an update using a subquery to correctly align the new child records with the new parent records.

If anyone has any enhancements or comments on this approach they would be
appreciated.

Thanks to everyone for your input.

AdamC Received on Tue Jun 24 2003 - 19:40:38 CDT