How to handle a million children - Inserting into child tables the easy way.

There are some situations where you want to insert a new EFFDT programatically into a parent row and also all of its child rows. Note you could also blend this with another article on this blog: SQL - Finding all child records for a given record.

Rather than write out a bunch of SQLExec statements complete with a list of fields that have to be maintainted each time you add a field to a child record you can get PeopleCode to do all the work, like this:



&arrRecs = Split("TABLE_A,TABLE_B,TABLE_C", ",");
For &i = 1 To &arrRecs.len

Local string &recName = &arrRecs [&i];

/* Get all rows for the parent/child record and process them. */
Local Rowset &rs = CreateRowset(@("Record." | &recName));
&rs.Fill("WHERE KEY_FIELD_1 = :1
AND FILL.EFFDT =
(SELECT MAX(F_ED.EFFDT)
FROM %Table(" | &recName | ") F_ED
WHERE F_ED.KEY_FIELD_1 = FILL.KEY_FIELD_1)"
, &keyValue);
For &j = 1 To &rs.ActiveRowCount

/* New Effective date for the row and insert. */
Local Record &rec = &rs(&j).GetRecord(@("Record." | &recName));
&rec.EFFDT.Value = AddToDate(%Date, 0, 0, 1);
&rec.Insert();

End-For;

End-For;



You could use the other post to dynamically create the list of child tables if you wish.