BULK INSERT SQL服务器数以百万计的记录 [英] Bulk Insert Sql Server millions of record
问题描述
我有接收数据流具有以下格式的Windows服务应用程序
I have a Windows Service application that receives a stream of data with the following format
IDX|20120512|075659|00000002|3|AALI |Astra Agro Lestari Tbk. |0|ORDI_PREOPEN|12 |00000001550.00|00000001291.67|00001574745000|00001574745000|00500|XDS1BXO1| |00001574745000|ݤ
IDX|20120512|075659|00000022|3|ALMI |Alumindo Light Metal Industry Tbk. |0|ORDI |33 |00000001300.00|00000001300.00|00000308000000|00000308000000|00500|--U3---2| |00000308000000|õÄ
这个数据来自于数百万行和序列 00000002 .... 00198562
,我必须分析,并根据序列到一个数据库表中插入他们。
This data comes in millions of rows and in sequence 00000002....00198562
and I have to parse and insert them according to the sequence into a database table.
我的问题是,什么是这些数据插入到我的数据库的最佳方式(最有效的)?我试图用一个简单的方法来打开SqlConnection对象然后生成SQL插入脚本的字符串,然后执行使用SqlCommand对象的脚本,不过这种方法花费的时间太长。
My question is, what is the best way (the most effective) to insert these data into my database? I have tried to use a simple method as to open a SqlConnection object then generate a string of SQL insert script and then execute the script using SqlCommand object, however this method is taking too long.
我看,我可以使用SQL BULK INSERT,但它已经从一个文本文件阅读,有可能是这种情况下使用BULK INSERT? (我从来没有使用过)。
I read that I can use Sql BULK INSERT but it has to read from a textfile, is it possible for this scenario to use BULK INSERT? (I have never used it before).
感谢您
更新:我知道SqlBulkCopy的,但它要求我有数据表第一,这是很好的表现?如果可能的话我希望在不必在内存中的DataTable使用从我的数据源到SQL Server直接插入。
update: I'm aware of SqlBulkCopy but it requires me to have DataTable first, is this good for performance? If possible I want to insert directly from my data source to SQL Server without having to use in memory DataTable.
推荐答案
如果您在C#写这个你可能想看看<一个href=\"http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx\">SqlBulkCopy类。
If you are writing this in C# you might want to look at the SqlBulkCopy class.
让您有效批量加载SQL Server表从另一个数据源。
Lets you efficiently bulk load a SQL Server table with data from another source.
这篇关于BULK INSERT SQL服务器数以百万计的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!