批量插入SQL Server表 [英] Batch inserts into SQL server table

问题描述

你好，
尝试在SQL Server的表中插入多行时出现性能问题.我正在解析txt文件中的行，并将每行插入到表格行中.问题是平均每个文件中有7k行，这使我的程序非常缓慢且不够用.有帮助吗?

Hello,
i am having a performance problem when trying to insert multiple rows into a table in SQL server. I am parsing lines in a txt file and inserting the lines each in a table row. The problem is that on average i have 7k plus lines in each file this is making my program very slow and not adequate. Any help ?

推荐答案

在内存中构建DataTable，然后使用SqlBulkCopy进行批量插入.查看以下示例 http: //blogs.msdn.com/b/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx [

Build a DataTable in-memory and then do a bulk insert using SqlBulkCopy. Check out this example http://blogs.msdn.com/b/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx[^]



Sorry, I read your comment, you have said that you are using a stored proc. Do you mean that you call a stored proc for every line you read? That''s always going to be costly. See if you could move to the approach I gave above. Else make sure you are not opening / closing the database connection for every line read/ every stored proc call...

一种方法是将所有查询一起构建为一个字符串，然后一次调用数据库.这样至少会减少每次连接到服务器然后执行插入/更新的性能拖累.

One way could be to build all your queries together in a string and then make a single call to the database. That will at least reduce the performance drag of connecting to your server every single time and then performing an insert / update.

您可以使用 bcp 从以下位置插入datat文件放入您的数据库

http://msdn.microsoft.com/en-us/library/aa174646(SQL .80).aspx [ ^ ]

you can use bcp to insert datat from file into your DB

http://msdn.microsoft.com/en-us/library/aa174646(SQL.80).aspx[^]

这篇关于批量插入SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！