portal entry

select a category, or use search below
(searches all categories and all time range)

Bulk Inserting Data

| View in Portal
November 17, 2017 10:04:44 PM GMT
One of the operations that we do often is insert multiple rows into a database.  Your inclination is probably to just loop over a cfquery and do the inserts.  While this will work it creates some excess overhead.   Your code may look something like this: <cfloop array="#users#" index="u"> <cfquery name="insertData"> insert into mytable (firstname, lastname, email) values ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#u.firstname#">, <cfqueryparam cfsqltype="cf_sql_varchar" value="#u.lastname#">, <cfqueryparam cfsqltype="cf_sql_varchar" value="#u.email#"> ) </cfquery></cfloop> While this fundamentally works it can cause performance issues.  Depending on configuration, […]
Labels: Blog, bulk insert, cfquery, cfqueryparam, loops, performance


In the case of the example using MSSQL2008, the maximum number of rows that can be inserted at a time this way is 1000. But if you're using query params, which you should be, you will likely encounter another issue first which is the 2100 parameter limit.
Comment by Kama Sama
2203 | July 31, 2019 02:29:17 AM GMT
The 2100 brick wall is exactly what happened to me.  But I like this angle for smaller tables.
Comment by yukonzach1
2270 | August 30, 2019 10:02:50 PM GMT
<p>I think you’ve got your closing cfquery and cfloop tags reversed in your bulk load.</p>
Comment by Spidey9000
4717 | April 02, 2020 08:22:49 PM GMT