从数组快速将数据导入SQLServer数据库的最佳方法是什么？

Question

问题：如何将数据从数组最有效地移动到 SQL Server 表中。

细节：我在 WinForms 应用程序中创建了一个具有许多行（通常约 100,000）和许多列（约 40）的数组。我需要以最快的方式将此数组放入相应的 SQL Server 表中。现在，我正在创建一个 SqlCommand 对象，循环遍历我的数组中的 100,000 行，并为每一行分配命令对象的 40 个参数，然后调用 ExecuteCommand。它可以工作，但速度很慢，肯定不是最有效的方法。我应该将数组中的所有数据放入 DataTable 中，然后以某种方式一次性发送数据表（我不知道如何做到这一点）？还是其他技术？写入文件并使用 bcp（似乎不会更快，我还没有尝试过）。任何建议都将不胜感激！

- Rob3C

Answer 1

SqlBulkCopy。如果能将内容存储在内存中作为DataTable，那就更好了，因为WriteToServer()方法的其中一个重载接受一个DataTable。

编辑：这里有一个示例，展示如何使用API。

- Robert C. Barth

Answer 2

我们使用XML处理大批量数据。将XML字符串传递给存储过程，然后由存储过程将XML拆分成表格。然后从临时表格中选择插入到您的数据库中。这对我们来说似乎非常有效。

编辑：如下面评论所述，将XML转换为表格的过程是sp_xml_preparedocument。

declare @idoc int
exec sp_xml_preparedocument @idoc output, @input_xml
-- select into a tmp table from openxml(@idoc, 'xpath_to_your_data')
exec sp_xml_removedocument @idoc

- Wes P

---

你知道SQL Server可以直接将XML读入表中，对吧？没有必要编写一个解析XML的存储过程。看看sp_xml_preparedocument就好了。 - Robert C. Barth

我们使用sp_xml_preparedocument，但我认为前面的“sp”是存储过程的意思，因此表明它确实是一个存储过程。我只是想不起来它的名字。谢谢澄清。 - Wes P

Answer 3

我最近读到了Pinal Dave使用一种奇怪的批量插入方法：

INSERT INTO MyTable  (FirstCol, SecondCol)
    SELECT  'First' ,1
    UNION ALL
SELECT  'Second' ,2
    UNION ALL
SELECT  'Third' ,3
...

考虑到你的大数据集，可以试一下看看会发生什么。

- David Thomas Garcia

---

这对于较少的行数来说还可以，但是对于10万行的批量加载会更快。 - ConcernedOfTunbridgeWells

Answer 4

• 在WinForms应用程序中以datatable的形式显示数据。
• 将数据复制到Excel（或OpenOffice Calc）中。
• 保存文件。
• 使用数据导入工具来提取数据并映射字段。

你可以在15分钟内完成。

编辑：哎呀，这可能不适用于100k行。但在尝试更复杂的东西之前，这是快速、简单且值得一试的方法。

- jcollum