将60m记录导入SQL最快的方法是什么
我的数据库中有 5-6 个表，我需要用测试数据填充这些表以测试应用程序的性能。
我可以编写代码并进行批量插入，我的预测是运行需要近 3 天的时间，所以我认为必须有一种更快的方法。
有什么想法吗？
I have a 5-6 tables in my database that I need to populate with test data to test peroformance in my app.
I can write a code and do a bulk insert my prediction is that it will take nearly 3 days to run so I assumed there must be a quicker way.
Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
根据您生成数据的方式，SSIS 可能是插入数据的最佳方式。
另外，请确保尽可能关闭所有触发器并删除尽可能多的索引，然后重新添加它们。
Depending on how you are generating your data, SSIS may be the best way to insert data.
Also, make sure you turn off all triggers where possible and remove as many indexes as possible, re-adding them afterwards.
SSIS 是一种方法，但您要么使用批量插入任务，要么循环遍历文件并调用 INSERT INTO 语句。有 BCP 可用。
SSIS is one way to go, but you'll either be using the Bulk Insert Task, or looping through the file and calling an INSERT INTO statement. There is BCP available.
BCP 非常非常快，并且使用起来并不困难，您也可以随后从批处理文件中简单快速地运行它。
SSIS 非常棒，您可以使用分支和决策树构建漂亮的脚本。
对于一次性情况，两者的速度相似。
您会对它能够处理 6000 万的事实感到震惊。有了一个整洁的BCP批处理文件，您可以导入，-us sql，然后修改，导出新数据，并创建不同的测试数据集。一切都将在几秒钟或几分钟内完成。
另请记住，如果需要为 bcp 指定格式文件，一种方法是在表中键入两条记录，然后从该表中导出 bcp，从而允许 bcp 生成 fmt 文件。现在您已经有了该文件，您可以使用它进行导入。
祝你好运，
马克·布林
爱尔兰
宝马 R80GS 1987
BCP is very very fast, and not that difficult to use, also you can run it simply and quickly from a batch file afterwards.
SSIS, is super, you can build nice scripts with branching and decision trees.
For a once off situation, the speed will be similar in both.
You will be shocked at how fact it can handle 60 million. Wiht a neat BCP batch file, you can import, - us sql then to modify, export the new data, and create different sets of test data. All will work in seconds or minutes.
Remember also, if you need to specify a format file for bcp, one way to do that is to key in two records into the table, then bcp export from that table, allowing bcp to generate a fmt file. Now you have the file, you can import using it.
Good Luck,
Mark Breen
Ireland
BMW R80GS 1987
通过预先禁用索引并在完成后重新启用它们，可以在插入大量数据时节省大量时间。在添加每一行时，它需要不断地重新平衡索引，有时会拆分页面等。最好跳过一堆重复操作，并在插入完成后让它完成一次工作。
如果您足够勇敢并且确定数据是正确的，您还可以删除任何外键关系，并在完成后将它们添加回来。否则，它将进行查找以检查插入的每行中每个 FK 值的有效性。其他约束也是如此。
You can save significant time when inserting tons of data by disabling indexes beforehand, and re-enabling them upon completion. While each row is being added, it needs to constantly rebalance the index, sometimes splitting pages, etc. Better to skip a bunch of repetitive manipulation, and let it do the work once when the inserts are done.
If you're brave and you're sure that the data is correct, you might also kill any foreign key relationships, and add them back on completion. Otherwise, it's going to do a lookup to check the validity of each FK value in each row inserted. Same goes for other constraints.
使用递归 CTE 一次生成并插入大量行：
Use recursive CTEs to generate and insert huge amounts of rows at a time:
您可以看一下 redgate 工具，他们有一个称为数据生成器的工具。它可能会帮助您不必编写自己的脚本，并且我确信他们已经以某种方式优化了插入速度。
（免责声明，我与 redgate 没有关系，就像该软件一样）。
You could have a look at the redgate tools, they have one called data generator. It may help you from having to write your own scripts and I'm sure they have this optimised somehow for speed of inserts.
(Disclaimer, I'm not associated with redgate, just like the software).