批量插入Sql Server数百万条记录

I have a Windows Service application that receives a stream of data with the following format

我有一个Windows服务应用程序，它接收如下格式的数据流

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|õÄ

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.

这个数据有数百万行和序列00000002 ....00198562，我必须根据序列对它们进行解析并将它们插入到数据库表中。

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.

我的问题是，将这些数据插入我的数据库的最佳方式(最有效的方式)是什么?我尝试过使用一个简单的方法来打开一个SqlConnection对象，然后生成一个SQL insert脚本字符串，然后使用SqlCommand对象执行脚本，但是这个方法花费的时间太长了。

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).

我读到我可以使用Sql大容量插入，但它必须从一个文本文件中读取，这个场景是否可能使用大容量插入?(我以前从未用过它)。

Thank you

谢谢你！

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.

更新:我知道SqlBulkCopy，但是它要求我首先具有DataTable，这对性能有好处吗?如果可能的话，我希望直接从数据源插入到SQL服务器，而不需要在内存中使用DataTable。

3 个解决方案

#1

If you are writing this in C# you might want to look at the SqlBulkCopy class.

如果您在c#中编写这个，您可能想要查看SqlBulkCopy类。

Lets you efficiently bulk load a SQL Server table with data from another source.

使您能够有效地批量加载使用来自另一个源的数据的SQL Server表。

#2

First, download free LumenWorks.Framework.IO.Csv library.

首先,免费下载LumenWorks.Framework.IO。Csv图书馆。

Second, use the code like this

第二，像这样使用代码

StreamReader sr = new TextReader(yourStream);
var sbc = new SqlBulkCopy(connectionString);
sbc.WriteToServer(new LumenWorks.Framework.IO.Csv.CsvReader(sr));

Yeah, it is really that easy.

是的，真的很简单。

#3

You Can Use SSIS "SQL Server Integration Service" For Convert Data From Source Data Flow To Distination Data Flow. Source Can Be Text File and Destination Can Be SQL Server Table File. and Convert Execute In Bulk Insert Mode.

您可以使用SSIS“SQL Server Integration Service”将数据从源数据流转换为扩展数据流。源文件可以是文本文件，目标文件可以是SQL Server Table文件。并转换成批量插入模式执行。

#1