如何使用ColdFusion进行批量插入有超过1000条记录？ [英] How to make batch insert with ColdFusion having more than 1000 records?

问题描述

我有一个包含大约3000条记录的电子表格。我需要将所有这些数据插入一个新表。所以在这种情况下使用批插入机制是相当不错的。

I am having a spreadsheet which contains around 3000 records. I need to insert all these data to a new table. So in this case using batch insert mechanism is quite good.

所以我试了一个简单的例子，

So i tried a simple example ,

 <cfquery datasource="cse">
    insert into Names
    values
    <cfloop from="1" to="3000" index="i">
        ('#i#')
        <cfif i LT 3000>, </cfif>
    </cfloop>
</cfquery>

但是由于SQL Server 2008每次只允许1000次批量插入，我收到错误。

But as SQL Server 2008 only allows 1000 batch insert at a time I am getting error.

那么如何制作每次包含999条记录的单独批次，并且一次可以执行？

So how to make separate batches each containing 999 records at a time and can execute at a time?

推荐答案

您可以使用 BULK INSERT

You can use a BULK INSERT statement that should cope with extremely large datasets.

  <cfquery datasource="cse">
    BULK INSERT Names
    FROM '#variables.sCSVLocation#'
  </cfquery>

如果您有理由不使用BULK INSERT 它下降到999的循环，那么你必须计算出数据集中有多少记录，除以999，得到你必须循环的次数。

If you have a reason not to use BULK INSERT and want to break it down into loops of 999, then you would have to work out how many 'records' are in the dataset, divide it by 999 to get the amount of times you'd have to loop over it.

这篇关于如何使用ColdFusion进行批量插入有超过1000条记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！