C# 批量插入Sql Server数百万条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10731179/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Bulk Insert Sql Server millions of record
提问by Arief
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....00198562and 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 插入脚本，然后使用 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 BULK INSERT 但它必须从文本文件中读取，这种情况是否可以使用 BULK INSERT？（我以前从未使用过它）。
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 Server，而不必在内存中使用 DataTable。
回答by Mark Byers
If you are writing this in C# you might want to look at the SqlBulkCopyclass.
如果您是用 C# 编写的，您可能需要查看SqlBulkCopy类。
Lets you efficiently bulk load a SQL Server table with data from another source.
使您可以使用来自其他源的数据高效地批量加载 SQL Server 表。
回答by mehdi lotfi
You can use SSIS "Sql Server Integration Service" for converting data from source data flow to destination data flow. The source can be a text file and destination can be a SQL Server table. Your conversion executes in bulk insert mode.
您可以使用 SSIS“Sql Server 集成服务”将数据从源数据流转换为目标数据流。源可以是文本文件，目标可以是 SQL Server 表。您的转换以批量插入模式执行。
回答by Vasyl Boroviak
First, download free LumenWorks.Framework.IO.Csvlibrary.
首先，下载免费的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.
是的，这真的很容易。
