Doing a bulk insert using cfquery in cfscript

dev support
1

I’m trying to do something like below to insert data into my DB.

INSERT INTO USERS VALUES
(2, 'Michael', 'Blythe'),
(3, 'Linda', 'Mitchell'),
(4, 'Jillian', 'Carson'),
(5, 'Garrett', 'Vargas');

I know how I can do this using tags (i.e., a cfloop inside the cfquery, using cfqueryparam’s).

How can I accomplish the same using the cfsscript version (i.e., queryExecute)? I know I can loop to create the SQL but how do I incorporate the cfqueryparam’s for multiple rows? I’d rather not skip the cfqueryparam/cfsqltype as best practice…

Thanks.

2

Almost all the tags in lucee also work in cfscript if you modify the syntax like below. You could use query still, and not need queryExecute. But for a query, the string has to be output with echo or writeoutput instead of just typed. You can also wrap tags in functions to make your own style of calling them.

query name="q" datasource="something"{
  echo("select ");  
for(field in something){
  queryparam value="#field#";
}
}

Sometimes you need preserveSingleQuotes if you are handling escaping yourself without cfqueryparam.

queryExecute is for copying prepared statement syntax of java, which is about putting ? where the input is, and then building an array of all the values to pass into that. cfquery does the same thing internally when you use cfqueryparam.

3

Try something like this:

data = [
  { ID: 2, firstname: "Michael", lastname: "Blythe" }
  ,{ ID: 3, firstname: "Linda", lastname: "Mitchell" }
  ,{ ID: 4, firstname: "Jillian", lastname: "Carson" }
];
params = {};
values = [];
loop collection="#data#" item="row" index="i"{
  param = {};
  param[ "ID#i#" ] = { value: row.ID, sqltype: "integer" };
  param[ "firstname#i#" ] = { value: row.firstname, sqltype: "varchar" };
  param[ "lastname#i#" ] = { value: row.lastname, sqltype: "varchar" };
  params.Append( param );
  values.Append( "( :ID#i#, :firstname#i#, :lastname#i# )" );
}
sql = "INSERT INTO users (ID, firstname, lastname ) VALUES #values.ToList()#;";
result = QueryExecute( sql, params );

(EDIT: Replaced two separate loops with just one to build both the params and sql string.)

2 Likes
4

Thanks Julian and Bruce.

That’s what I figured needed to be done but was concerned about creating a params struct with 10,000+ variables in it since they don’t get bound into the sql until queryExecute.

For the time being, I’m using “(#value1#, #value2#, #value3#)” without param building since the source data is my own anyways and trusted. Will try the two alternates with param binding above and compare the performance.