ColdFusion：如何在一个插入中插入带有静态外键的列表？ [英] ColdFusion: How to insert a list with a static foreign key in one insert?

问题描述

我有一个像这样的东西1，thing2，thing3。我想将它们插入到具有相同外键的查找表中。所以理想情况下，它看起来像这样：

 < cfquery datasource =＃ds＃result =insert_things> 
 INSERT INTO lkp_things（foreign_key，thing）VALUES 
（1，thing1），（1，thing2），（1，thing3）
< / cfquery> 
  

似乎唯一的方法是将列表转换为查询，但我想知道，是否有更简单的方法？

这是我试过的：

 < 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> 
  

我听说你不能在cfquery里做cfloop，甚至确定如果这是真的，因为我不能在VALUES中有一个尾随逗号，我不知道如何说当前迭代数在cfloop内。如果我把列表变成一个查询，那么我可以做currentRow，但是，我想知道是否有一个更简单的方法来完成这一切，在我经历所有这一切。

此外，我使用CF 8和sql server '08编辑：对不起，我实际上是使用2000年。

解决方案

更新：

最后，真正的问题是，插入多组值的功能只有一个 VALUES 子句仅在SQL Server 2008+中受支持，OP正在使用2000.因此，它们与

$ b

b

当然你可以在 cfquery 中循环。所有cfml代码首先在CF服务器上处理。然后将生成的SQL字符串发送到数据库以供执行。只要你的CF代码产生一个有效的SQL语句，你可以做任何你想要的:)无论你应该是一个不同的问题，但这种循环是完全正常的。

回到您的问题，只需切换到从/到循环，并使用列表函数 getToken（list，index）以获取各个元素（请参阅 Matt的示例 ）或使用数组。显然你还应该验证列表不是空的。我个人的喜好是数组。未测试，但类似如下：

 < cfset thingArray = listToArray（things，，）> 
 
< cfquery datasource =＃ds＃result =insert_things> 
 INSERT INTO lkp_things（foreign_key，thing）VALUES 
< cfloop from =1to =＃arrayLen（thingArray）＃index =x> 
< cfif x gt 1>，< / cfif> 
（
<！---注意：将cfsqltype =...替换为正确的类型---> 
< cfqueryparam value =＃id＃cfsqltype = ...> 
，< cfqueryparam value =＃thingArray [x]＃cfsqltype =...> 
）
< / cfloop> 
< / cfquery> 
  

说到这里，你的 #thing＃ code> list？如果这些值存在于数据库表中，您可以直接使用 SELECT 语句而不是循环插入它们：

  INSERT INTO lkp_things（foreign_key，thing）
 SELECT< cfqueryparam value =＃id＃cfsqltype =...>，thing 
 FROM ThingTable 
 WHERE thing IN 
（
< cfqueryparam value =＃thingList＃list =truecfsqltype =...> 
）
  




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.

解决方案

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="..."> 
              )

这篇关于ColdFusion：如何在一个插入中插入带有静态外键的列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！