I was writing some code for a client that processed a csv file and inserted each row into a database, when there were a couple hundred rows the processing time was very quick, but when there are 8000 rows this can take quite a while even on a very fast database, so I wrote up some code to batch insert the records (which I've done too many times).

I was using MSSQL, so the max parameters I could send at one time was 2100 (210 rows x 10 columns), but your mileage may vary. I'd set rowsPerInsert as high as possible and decrease as necessary. You should be able to easily modify the pseudo code below.

view plain print about
1<cfscript>
2 totalrows = 5000; // this would come from a structCount or something similar
3
rowsPerInsert = 210;
4 //this creates a struct with alternating values of true/false for test purposes
5
stRows = {};
6 for (i=1; i
<=totalRows; i++) {
7 stRows[i] = i MOD 2 ? true : false;
8 }
9</cfscript>
10<cfoutput>
11<cfloop from="0" to="#totalRows - 1#" step="#rowsPerInsert#" index="totalRow">
12    <br><br>INSERT #totalRow#<br>
13    <cfloop from="1" to="#rowsPerInsert#" index="indRow">
14        <cfset rowNumber = totalRow + indRow>
15        <cfif rowNumber LTE totalRows>
16            #rowNumber# #stRows[rowNumber]# <!--- SELECT goes here --->
17            <cfif indRow NEQ rowsPerInsert AND rowNumber NEQ totalRows> UNION ALL</cfif>
18            <br>
19        </cfif>
20    </cfloop>
21</cfloop>
22</cfoutput>

This is mostly for me to steal from myself once I need this again, but hopefully you find it useful as well.