ColdFusion: How to insert a list with a static foreign key in one insert?

Question

I have a list like this thing1,thing2,thing3. And I want to insert them into a look-up table with the same foreign key. So ideally it would look like this:

<cfquery datasource="#ds#" result="insert_things">
    INSERT INTO lkp_things (foreign_key, thing) VALUES
    (1, thing1), (1, thing2), (1, thing3)
</cfquery>

It seems like the only way to accomplish this is to turn the list into a query, but I'm wondering, is there is a simpler way?

Here's what I've tried:

<cfquery datasource="#ds#" result="insert_things">
    INSERT INTO lkp_things (foreign_key, thing) VALUES
    <cfloop list="#things#" index="thing">
        (#id#,#thing#)<cfif ?????? NEQ len(#things#)>,</cfif>
    </cfloop>
</cfquery>

I've heard that you can't do a cfloop inside a cfquery, but I'm not even sure if that's true because I can't have a trailing comma in the VALUES, and I'm not sure how to say "The current iteration number" inside a cfloop. If I turned the list into a query, then I could do currentRow, but again, I'd like to know if there's a simpler way to accomplish this before I go through all that.

Also, I'm using CF 8 and sql server '08 EDIT: Sorry, I'm actually using 2000.

score 4 · Accepted Answer · edited May 23 '17 at 11:59

4

Update:

Ultimately the real problem here was that the feature of inserting multiple sets of values with a single VALUES clause is only supported in SQL Server 2008+ and the OP is using 2000. So they went with the select / union all approach instead.

(Expanded from the comments)

Sure you can loop inside a cfquery. All cfml code is processed on the CF server first. Then the resulting SQL string is sent to the database for execution. As long as your CF code results in a valid SQL statement, you can do just about anything you want :) Whether you should is a different question, but this kind of looping is perfectly fine.

Getting back to your question, just switch to a from/to loop instead and use list functions like getToken(list, index) to get the individual elements (see Matt's example) or use an array instead. Obviously you should also validate the list is not empty first. My personal preference is arrays. Not tested, but something like this:

    <cfset thingArray = listToArray(things, ",")>

    <cfquery datasource="#ds#" result="insert_things">
       INSERT INTO lkp_things (foreign_key, thing) VALUES
       <cfloop from="1" to="#arrayLen(thingArray)#" index="x">
           <cfif x gt 1>,</cfif>
           ( 
              <!--- Note: Replace cfsqltype="..." with correct type --->
               <cfqueryparam value="#id#" cfsqltype="..."> 
             , <cfqueryparam value="#thingArray[x]#" cfsqltype="..."> 
           )
       </cfloop>
    </cfquery>

Having said that, what is the source of your #thing# list? If those values exist in a database table, you could insert them directly with a SELECT statement, instead of a loop:

       INSERT INTO lkp_things (foreign_key, thing) 
       SELECT <cfqueryparam value="#id#" cfsqltype="...">, thing
       FROM   ThingTable
       WHERE  thing IN 
              (
                <cfqueryparam value="#thingList#" list="true" cfsqltype="..."> 
              )

edited May 23 '17 at 11:59

Community

1
1

answered Oct 25 '13 at 14:15

Leigh

28,765
10
55
103

1

+1 for using the `list` attribute of the `cfqueryparam` tag (and of course your detail). No looping necessary... – Miguel-F Oct 25 '13 at 14:17
Well I do not know if it applies here or not :) But the loop-free `insert/select` technique is a simple approach a lot of people overlook, so I figured I would mention it just in case. – Leigh Oct 25 '13 at 14:26
I don't even think the `SELECT` is necessary in this case. The OP mentioned that the `id` does not change and simply using `cfqueryparam` with the `list` attribute should work. – Miguel-F Oct 25 '13 at 14:27
I am not sure that would work to generate the proper pairs of values. Did you try it out? (I cannot test it right now). Though fyi, I specifically wanted to use a `SELECT` here. Aside from generating the correct values, it also acts as a bit of server side validation. Invalid values that are not in the db table are deliberately ignored. – Leigh Oct 25 '13 at 14:35
No I did not try it out and you are correct about not generating the pairs of values correctly. Guess I didn't think that through completely. Anyway your answer should do the trick if the things are coming from a table. – Miguel-F Oct 25 '13 at 14:39
The list is not coming from a database, unfortunately. – Travis Heeter Oct 25 '13 at 15:37
Ah, okay. Well it may come in handy in the future :) – Leigh Oct 25 '13 at 16:26
This didn't work for some reason. SQL Server didn't like it. I had to use a SELECT / UNION ALL solution from here: http://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part – Travis Heeter Oct 25 '13 at 17:48
@TravisHeeter - No, it definitely works. I have used the approach many times. Though as I mentioned above, the example is not tested. I am not near a CF install. Update your question with the code that "didn't work" (and any error messages) and I can help you spot the problem. – Leigh Oct 25 '13 at 18:01
@Leigh was getting this error: Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near ','. With this: INSERT INTO lkp_Things (foreign_key,thing) VALUES (1,'thing1'),(1,'thing2') I tried a million different ways, nothing worked. – Travis Heeter Oct 25 '13 at 18:03
Two possible causes: 1) I just noticed a typo. I missed the closing quote after the value and before the cfsqltype and 2) Did you remember to fill in your cfsqltypes? Since I do not know your data types, I marked it with the placeholder "..." – Leigh Oct 25 '13 at 18:11
I ran the resulting query in the database. – Travis Heeter Oct 25 '13 at 18:12
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/40012/discussion-between-leigh-and-travis-heeter) – Leigh Oct 25 '13 at 18:12

score 2 · Answer 2 · answered Oct 25 '13 at 14:03

I would add a counter and increment it inside the loop. Also you need to use listLen() rather than len on your list to get the number of items.

<cfquery datasource="#ds#" result="insert_things">
  INSERT INTO lkp_things (foreign_key, thing) VALUES
  <cfset count = 1>
  <cfloop list="#things#" index="thing">
    (
     <cfqueryparam cf_sql_type="cf_sql_integer" value="#id#">,
     <cfqueryparam cf_sql_type="cf_sql_varchar" value="#thing#">
    )<cfif count NEQ listLen(things)>,</cfif>
   <cfset count++>
  </cfloop>
</cfquery>

You should use cfqueryparam on all your values. I've guessed on what type the column is.

score 1 · Answer 3 · edited May 23 '17 at 12:15

I was getting a database error with

INSERT INTO lkp_things (foreign_key,thing) 
VALUES (1,'thing1'),(1,'thing2')

Leigh helped me realize that I was actually using SQL Server '00, and that version does not allow this method of insertion. So, I had to use this:

INSERT INTO lkp_things (foreign_key,thing)
SELECT 1,'thing1'
    UNION ALL
SELECT 1,'thing2'

In CF that looks like this:

<cfset thingArray = listToArray(form.things,",")>
<cfquery datasource="aliaba_jdbc" name="insert_courses">
    INSERT INTO lkp_Things (id,thing)
    <cfloop from="1" to="#arrayLen(thingArray)#" index="x">
        SELECT <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.id#">,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#thingArray[x]#">
        <cfif x lt arrayLen(thingArray)>UNION ALL</cfif>
    </cfloop>
</cfquery>

I found this solution here: How do I insert multiple rows WITHOUT repeating the "INSERT INTO dbo.Blah" part of the statement?

Just as an FYI for future searchers, the ability to insert multiple sets of values with a *single* `VALUES` clause requires SQL Server 2008+. — Leigh, Oct 26 '13 at 17:29

score 0 · Answer 4 · answered Oct 25 '13 at 14:16

This answer is similar to Matt's but does not use conditional logic.

<cfquery>
insert into lkp_things (foreign_key, thing)
select 0, ''
where 1=2
<cfloop list="#things#" index="thing">
union
select <cfqueryparam cf_sql_type="cf_sql_integer" value="#id#">
, <cfqueryparam cf_sql_type="cf_sql_varchar" value="#thing#">
</cfloop>

Both our answers assume that the id variable does not change and was set somewhere else.

ColdFusion: How to insert a list with a static foreign key in one insert?

4 Answers4

Linked

Related