Adobe Campaign - Looping through more than 10k records in queryDef

var limit = 5000;
var loop = true;
var lastProcessed = 0;
var info = 'Value to insert';

logInfo("Starting script");

while(loop){
logInfo("Loop commencing");

var query = xtk.queryDef.create(
<queryDef schema={vars.targetSchema} operation="select" lineCount={limit}>
<select>
<node expr="@id"/>
<node expr="tempColumn"/>
</select>
<where>
<condition expr={"[@id] > " + lastProcessed}/>
</where>
<orderBy>
<node expr="@id"/>
</orderBy>
</queryDef>);

result = query.ExecuteQuery();

if(Object.keys(result).length > 0){
for each (row in result){
var sql = "UPDATE " + vars.tableName + " SET stempColumn=" + info + " WHERE iId='" + row.@id + "'";
sqlExec(sql);
lastProcessed = row.@id;}
}else{
logInfo("Terminating.")
loop = false;}
}
 

This is a bit of a messy script as I spent a bunch of time tinkering and getting it to work. BUT essentially to overcome the inbuilt 10K record limit on the queryDef function and potential memory usage issues, we run the whole thing within a loop. Rows 1-4 define variables which will indicate how many records to retrieve in one loop, store what row was the last one processed, some text to be inserted and a boolean TRUE value to be used to control the loop. Row 10-22 is the queryDef which will create the table object which can be accessed. This is similar to what was run in the other “how to iterate page” but includes a “where” condition - that says the primary key value must be higher than the last one processed, which is captured in the variable. I initially struggled getting this to work, because the selection would not be consistent - like random rows would be selected, because adobe campaign doesn’t have any sort of line indication - so the solution I patched together is to order the information by the primary key value! The row iteration on 26-33 checks to see how many results are in the current batch, if there’re more than 0, it’ll activate but otherwise will just activate the else loop, set the trigger variable to false and end the script. Rows 28/29 is just me inserting that text into the column, but it could be literally anything. Row 30 captures the primary key value that was last processed, which means the next iteration will be able to advance down the list.