Bulk Insert Sql Server millions of record

问题:

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

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.

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.

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

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.

回答1:

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

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

回答2:

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

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.