批量插入SQL Server表 [英] Batch inserts into SQL server table
本文介绍了批量插入SQL Server表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
你好,
尝试在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屋!
查看全文