ColdFusion:如何在一次插入中插入具有静态外键的列表?

2022-01-09 00:00:00 sql sql-server coldfusion

我有一个像这样的东西1，东西2，东西3的列表.我想将它们插入到具有相同外键的查找表中.所以理想情况下它应该是这样的:

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>

似乎实现这一点的唯一方法是将列表转换为查询，但我想知道，有没有更简单的方法?

这是我尝试过的:

<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，但是我想知道在我做这一切之前是否有更简单的方法来完成这个.

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.

另外，我使用的是 CF 8 和 sql server '08 抱歉，我实际上使用的是 2000.

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

推荐答案

更新:

最终真正的问题是使用单个 VALUES 子句插入多组值的功能仅在 SQL Server 2008+ 中受支持，并且 OP 使用的是 2000.所以他们选择了选择/联合所有方法.

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.

(根据评论展开)

当然你可以在 cfquery 中循环.首先在 CF 服务器上处理所有 cfml 代码.然后将生成的 SQL 字符串发送到数据库执行.只要您的 CF 代码生成有效的 SQL 语句，您就可以做任何您想做的事情:) 是否应该是另一个问题，但是这种循环非常好.

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.

回到您的问题，只需切换到 from/to 循环并使用 getToken(list, index) 等列表函数来获取各个元素(请参阅Matt 的示例)或使用数组代替.显然，您还应该首先验证列表不为空.我个人的偏好是数组.未经测试，但类似这样:

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>

话虽如此，您的 #thing# 列表的来源是什么?如果这些值存在于数据库表中，您可以使用 SELECT 语句直接插入它们，而不是循环:

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

相关文章