C# 快速向 SQL Server 插入 200 万行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13722014/
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
Insert 2 million rows into SQL Server quickly
提问by Wadhawan Vishal
I have to insert about 2 million rows from a text file.
我必须从文本文件中插入大约 200 万行。
And with inserting I have to create some master tables.
通过插入，我必须创建一些主表。
What is the best and fast way to insert such a large set of data into SQL Server?
将如此大量的数据插入 SQL Server 的最佳和快速方法是什么？
采纳答案by Soner G?nül
You can try with SqlBulkCopyclass.
你可以试试SqlBulkCopy上课。
Lets you efficiently bulk load a SQL Server table with data from another source.
使您可以使用来自其他源的数据高效地批量加载 SQL Server 表。
There is a cool blog postabout how you can use it.
有一篇很酷的博客文章介绍了如何使用它。
回答by Pranay Rana
I think its better you read data of text file in DataSet
Try out SqlBulkCopy- Bulk Insert into SQL from C# App
// connect to SQL using (SqlConnection connection = new SqlConnection(connString)) { // make sure to enable triggers // more on triggers in next post SqlBulkCopy bulkCopy = new SqlBulkCopy( connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null ); // set the destination table name bulkCopy.DestinationTableName = this.tableName; connection.Open(); // write the data in the "dataTable" bulkCopy.WriteToServer(dataTable); connection.Close(); } // reset this.dataTable.Clear();
我认为最好在 DataSet 中读取文本文件的数据
试用 SqlBulkCopy-从 C# 应用批量插入 SQL
// connect to SQL using (SqlConnection connection = new SqlConnection(connString)) { // make sure to enable triggers // more on triggers in next post SqlBulkCopy bulkCopy = new SqlBulkCopy( connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null ); // set the destination table name bulkCopy.DestinationTableName = this.tableName; connection.Open(); // write the data in the "dataTable" bulkCopy.WriteToServer(dataTable); connection.Close(); } // reset this.dataTable.Clear();
or
或者
after doing step 1 at the top
在顶部执行步骤 1 后
- Create XML from DataSet
- Pass XML to database and do bulk insert
- 从数据集创建 XML
- 将 XML 传递给数据库并进行批量插入
you can check this article for detail : Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function
您可以查看这篇文章的详细信息：使用 C# DataTable 和 SQL 服务器 OpenXML 函数批量插入数据
But its not tested with 2 million record, it will do but consume memory on machine as you have to load 2 million record and insert it.
但是它没有用 200 万条记录进行测试，它只会消耗机器上的内存，因为您必须加载 200 万条记录并插入它。
回答by Techie Joe
I ran into this scenario recently (well over 7 million rows) and eneded up using sqlcmd via powershell (after parsing raw data into SQL insert statements) in segments of 5,000 at a time (SQL can't handle 7 million lines in one lump job or even 500,000 lines for that matter unless its broken down into smaller 5K pieces. You can then run each 5K script one after the other.) as I needed to leverage the new sequence command in SQL Server 2012 Enterprise. I couldn't find a programatic way to insert seven million rows of data quickly and efficiently with said sequence command.
我最近遇到了这种情况（超过 700 万行）并通过 powershell 使用 sqlcmd（在将原始数据解析为 SQL 插入语句之后）一次以 5,000 段为单位（SQL 无法在一次作业中处理 700 万行）甚至 500,000 行，除非将其分解为更小的 5K 片段。然后您可以一个接一个地运行每个 5K 脚本。）因为我需要利用 SQL Server 2012 Enterprise 中的新序列命令。我找不到使用上述序列命令快速有效地插入 700 万行数据的编程方法。
Secondly, one of the things to look out for when inserting a million rows or more of data in one sitting is the CPU and memory consumption (mostly memory) during the insert process. SQL will eat up memory/CPU with a job of this magnitude without releasing said processes. Needless to say if you don't have enough processing power or memory on your server you can crash it pretty easily in a short time (which I found out the hard way). If you get to the point to where your memory consumption is over 70-75% just reboot the server and the processes will be released back to normal.
其次，一次性插入一百万行或更多数据时要注意的一件事是插入过程中的 CPU 和内存消耗（主要是内存）。SQL 将在不释放所述进程的情况下，以这种规模的工作占用内存/CPU。不用说，如果您的服务器上没有足够的处理能力或内存，您很容易在短时间内崩溃（我发现这很困难）。如果您的内存消耗超过 70-75%，只需重新启动服务器，进程就会恢复正常。
I had to run a bunch of trial and error tests to see what the limits for my server was (given the limited CPU/Memory resources to work with) before I could actually have a final execution plan. I would suggest you do the same in a test environment before rolling this out into production.
在我真正制定最终执行计划之前，我必须运行一堆反复试验来查看我的服务器的限制是什么（考虑到有限的 CPU/内存资源）。我建议您在将其投入生产之前在测试环境中执行相同的操作。
回答by Bill Edmett
I use the bcp utility. (Bulk Copy Program) I load about 1.5 million text records each month. Each text record is 800 characters wide. On my server, it takes about 30 seconds to add the 1.5 million text records into a SQL Server table.
我使用 bcp 实用程序。（批量复制程序）我每个月加载大约 150 万条文本记录。每个文本记录的宽度为 800 个字符。在我的服务器上，将 150 万条文本记录添加到 SQL Server 表中大约需要 30 秒。
The instructions for bcp are at http://msdn.microsoft.com/en-us/library/ms162802.aspx
bcp 的说明位于http://msdn.microsoft.com/en-us/library/ms162802.aspx
回答by Amir
Re the solution for SqlBulkCopy:
重新 SqlBulkCopy 的解决方案：
I used the StreamReader to convert and process the text file. The result was a list of my object.
我使用 StreamReader 来转换和处理文本文件。结果是我的对象列表。
I created a class than takes Datatableor a List<T>and a Buffer size (CommitBatchSize). It will convert the list to a data table using an extension (in the second class).
我创建了一个类，而不是 takeDatatable或 aList<T>和一个缓冲区大小 ( CommitBatchSize)。它将使用扩展名（在第二类中）将列表转换为数据表。
It works very fast. On my PC, I am able to insert more than 10 million complicated records in less than 10 seconds.
它的工作速度非常快。在我的 PC 上，我能够在不到 10 秒的时间内插入超过 1000 万条复杂的记录。
Here is the class:
这是课程：
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DAL
{
public class BulkUploadToSql<T>
{
public IList<T> InternalStore { get; set; }
public string TableName { get; set; }
public int CommitBatchSize { get; set; }=1000;
public string ConnectionString { get; set; }
public void Commit()
{
if (InternalStore.Count>0)
{
DataTable dt;
int numberOfPages = (InternalStore.Count / CommitBatchSize) + (InternalStore.Count % CommitBatchSize == 0 ? 0 : 1);
for (int pageIndex = 0; pageIndex < numberOfPages; pageIndex++)
{
dt= InternalStore.Skip(pageIndex * CommitBatchSize).Take(CommitBatchSize).ToDataTable();
BulkInsert(dt);
}
}
}
public void BulkInsert(DataTable dt)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
// make sure to enable triggers
// more on triggers in next post
SqlBulkCopy bulkCopy =
new SqlBulkCopy
(
connection,
SqlBulkCopyOptions.TableLock |
SqlBulkCopyOptions.FireTriggers |
SqlBulkCopyOptions.UseInternalTransaction,
null
);
// set the destination table name
bulkCopy.DestinationTableName = TableName;
connection.Open();
// write the data in the "dataTable"
bulkCopy.WriteToServer(dt);
connection.Close();
}
// reset
//this.dataTable.Clear();
}
}
public static class BulkUploadToSqlHelper
{
public static DataTable ToDataTable<T>(this IEnumerable<T> data)
{
PropertyDescriptorCollection properties =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
table.Rows.Add(row);
}
return table;
}
}
}
}
Here is an example when I want to insert a List of my custom object List<PuckDetection>(ListDetections):
这是我想插入自定义对象列表List<PuckDetection>( ListDetections)时的示例：
var objBulk = new BulkUploadToSql<PuckDetection>()
{
InternalStore = ListDetections,
TableName= "PuckDetections",
CommitBatchSize=1000,
ConnectionString="ENTER YOU CONNECTION STRING"
};
objBulk.Commit();
The BulkInsertclass can be modified to add column mapping if required. Example you have an Identity key as first column.(this assuming that the column names in the datatable are the same as the database)
的BulkInsert类可以修改，如果需要添加的列映射。例如，您将 Identity 键作为第一列。（假设数据表中的列名与数据库相同）
//ADD COLUMN MAPPING
foreach (DataColumn col in dt.Columns)
{
bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
回答by Amey Vartak
I tried with this method and it significantly reduced my database insert execution time.
我尝试过这种方法，它大大减少了我的数据库插入执行时间。
List<string> toinsert = new List<string>();
StringBuilder insertCmd = new StringBuilder("INSERT INTO tabblename (col1, col2, col3) VALUES ");
foreach (var row in rows)
{
// the point here is to keep values quoted and avoid SQL injection
var first = row.First.Replace("'", "''")
var second = row.Second.Replace("'", "''")
var third = row.Third.Replace("'", "''")
toinsert.Add(string.Format("( '{0}', '{1}', '{2}' )", first, second, third));
}
if (toinsert.Count != 0)
{
insertCmd.Append(string.Join(",", toinsert));
insertCmd.Append(";");
}
using (MySqlCommand myCmd = new MySqlCommand(insertCmd.ToString(), SQLconnectionObject))
{
myCmd.CommandType = CommandType.Text;
myCmd.ExecuteNonQuery();
}
*Create SQL connection object and replace it where I have written SQLconnectionObject.
*创建 SQL 连接对象并在我编写 SQLconnectionObject 的地方替换它。
