I realize this particular requirement might be rare, but it comes in handy when you have an XREF (cross-reference) table which ties two tables together. Many times, when this is the case, it is a common requirement to perform multiple SQL inserts into the table within one request.

If we are performing the inserts from an AJAX call, we might have the required insert data right in our query parameters. This would allow us to loop over the url structure to perform a bulk update.

But wait! There's one problem. When you loop over a structure, you don't know where the loop ends, so you really don't know when to stop placing that comma between insert blocks.

For instance

INSERT INTO table (key, value)
VALUES ('key1', 'value1'), ('key2', 'value2'), ('key3', value3)

So with a simple loop, you could do the following:

<cfset sql = "INSERT INTO table (key, value) VALUES">
<cfloop collection="#url#" item="key">
  <cfset sql = "(#key#, #url[key]#),">
</cfloop>
<cfset sql = Left(sql, len(sql) - 1)>  <!--- to get rid of that trailing comma --->

Then, run the query in a <cfquery> statement.

But, wait again! What about securing our queries with <cfqueryparam>? That's a problem here because <cfqueryparam> can only be nested inside a <cfquery> block.

That's where <cfsavecontent> comes in. See the example below:

<cfquery>
    INSERT INTO table
    (
        key,
        value,
    )
    VALUES
    <cfsavecontent variable="inserts">
    <cfloop collection="#url#" item="key">
        (
            <cfqueryparam value="#key#">,
            <cfqueryparam value="#url[key]#">
        ),</cfloop></cfsavecontent>
    #Left(inserts, len(inserts) - 2)#
</cfquery>

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *