从平面文件向SQL Server插入数百万条记录存在哪些陷阱？

Question

我即将开始编写一个Windows窗体应用程序，它将打开一个大约230MB大小的管道分隔的txt文件。然后，该应用程序将把这些数据插入到SQL Server 2005数据库中（显然需要快速完成）。我在这个项目中使用C# 3.0和.NET 3.5。

我不是要求提供应用程序，只是想寻求一些共同的建议和潜在风险的建议。从网站上收集的信息显示，SQL批量复制是必需的，是否还有其他需要考虑的问题（我认为仅使用窗体应用程序打开txt文件将是一个巨大的工程；也许可以将其拆分为Blob数据）。

谢谢，如果有人需要，我会编辑问题以便更清晰地表达。

- RyanKeeter

Answer 1

你需要编写一个winforms应用程序吗？使用SSIS可能会更简单、更快速。特别是可以使用一些内置任务，例如Bulk Insert task。

另外，值得查看的是SQL Server 2005中的平面文件批量导入方法速度比较。

更新：如果您是SSIS的新手，请查看以下网站，以便快速上手：1）SSIS控制流基础知识 2）开始学习SQL Server Integration Services

这是关于如何使用Integration Services将Excel文件导入到SQL Server 2005的另一个教程。

- Gulzar

---

在SSIS中要容易得多，而且速度也更快...同意。 - EvilSyn

我发现SSIS非常麻烦。它经常因为奇怪的原因而崩溃，需要DBA访问数据库服务器来进行故障排除/修复/重新运行（这在我们的生产环境中是受限制的）。 - Ron Savage

我同意这需要一些掌握，特别是在故障排除和部署方面。 - Gulzar Nazim

我真的很喜欢这个想法，Gulzar。你有什么其他超级链接可以帮助这个项目吗？你提供的两个链接都非常棒。 - RyanKeeter

@Ron：我完全同意。由于我曾经使用过DTS并且非常了解它，所以我无法忍受SSIS，并放弃了像这个问题所问的大型项目。数百万行从我无法控制其创建的带有异常的平面文件中插入，而SSIS则会失败。 - Taptronic

Answer 2

这将是一个流式处理的尝试。

如果可以的话，请不要在此处使用事务。事务成本将会太高。

因此，您将逐行读取文件并逐行插入。您应该将失败的插入转储到另一个文件中，以便稍后诊断并查看它们失败的位置。

首先，我建议您尝试批量插入几百行，以确保流式处理正常工作，然后您可以随意打开。

- Orion Adrian

Answer 3

你可以尝试使用SqlBulkCopy。它可以从“任何数据源”中提取数据。

- Kyralessa

Answer 4

顺带一提，有时候在进行批量插入操作之前删除表的索引并在操作完成后重新创建索引会更快。

- vIceBerg

Answer 5

你可以考虑从完全恢复模式切换到批量日志记录模式。这将有助于保持备份文件的合理大小。

- Dave DuPlantis

---

我认为批量记录模式可以使日志文件变小，但会使日志备份变大。难道MSDN没有这样说吗？（http://msdn.microsoft.com/en-us/library/ms175987.aspx）？ - pkmiec

不，它提醒您批量记录恢复与完全恢复并没有太大区别，除了一些操作。如果您没有使用这些操作，那么您基本上正在执行完全恢复。在这种情况下，OP可以使用批量记录恢复进行BULK INSERT以避免记录这些插入（知道如果需要可能会重复插入）。 - Dave DuPlantis

Answer 6

我完全推荐使用SSIS，您可以在相对较短的时间内读取数百万条记录并清理它们。

您需要花些时间来掌握SSIS，但这将会有所回报。这里还有一些其他的SO线程可能会有用：

什么是在SQL Server（C#客户端）中批量插入大量数据的最快方法？

SSIS的推荐学习材料是什么？

您还可以从C#创建一个包。我有一个C#程序，它从遗留系统中读取3GL“主文件”（使用我为相关项目开发的API解析为对象模型），获取一个包模板并修改它以生成ETL包。

- Cade Roux

Answer 7

如果数据文件的列格式与需要存储数据的目标表匹配，我更喜欢使用命令行实用程序来加载数据文件。它非常快速，你可以为任何未能插入的“奇怪”记录指定错误文件。

如果您需要存储命令行参数（服务器、数据库、用户名/密码或信任连接、表格、错误文件等），则您的应用程序可以启动该命令。

我喜欢这种方法胜过运行BULK INSERT SQL命令，因为不需要将数据文件放在数据库服务器可以访问的系统上。要使用批量插入，必须指定要加载的数据文件路径，因此必须是数据库服务器上正在运行载入的系统用户可见且可读的路径。通常对我来说太麻烦了。 :-)

- Ron Savage

Answer 8

你所说的数据大小其实并不是那么巨大。我不知道你的效率问题是什么，但如果你可以等待几个小时进行插入，你可能会惊讶于使用一种非常天真的技术，即逐行插入每一行数据，这将变得非常容易。将一千左右的行分批提交到SQL服务器可能会使它更快。

这只是一个建议，如果你不需要它尽可能快的话，可以节省你大量的编程时间。根据这个导入需要运行的频率，为了等待它运行而节省几天的编程时间很容易是值得的。

- user12861

Answer 9

你可以使用SSIS进行读取和插入，但是从WinForms应用程序中调用它作为一个包。然后，您可以将源、目标、连接字符串等作为参数/配置传递进去。

如何操作：http://msdn.microsoft.com/en-us/library/aa337077.aspx 您可以在SSIS中设置转换和错误处理，甚至可以根据输入参数创建逻辑分支。

- Meff