SqlBulkCopy快速批量大数据插入

测试数据表

CREATE TABLE [dbo].[Demo](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Time] [datetime] NULL,
    [Name] [nvarchar](50) NULL,
    [Has] [bit] NULL,
    [Age] [int] NULL
) ON [PRIMARY]

 

 

.Net批量插入

private void batchInsert()
{
            DataTable dt = new DataTable("demo");
            dt.Columns.Add("ID", typeof(System.Int32)); //虽然ID为自增字段，但仍然要添加列，否则会报异常“来自数据源的 String 类型的给定值不能转换为指定目标列的类型 datetime。”
            dt.Columns.Add("Time", typeof(System.DateTime));
            dt.Columns.Add("Name", typeof(System.String));
            dt.Columns.Add("Has", typeof(System.Int32));
            dt.Columns.Add("Age", typeof(System.Int32));

            for (int i = 0; i < 100000; i++)
            {
                DataRow dr = dt.NewRow();
                dr["Time"] = DateTime.Now;
                dr["Name"] = "Demo";
                dr["Has"] = 1;
                dr["Age"] = 10;
                dt.Rows.Add(dr);
            }
            AddDataTable(dt);
}

public bool AddDataTable(DataTable dt)
{
            using (SqlConnection conn = new SqlConnection(sqlHelper.SQLConString))
            {
                SqlTransaction transaction = null;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, transaction);
                    sbc.DestinationTableName = dt.TableName;
                    sbc.BatchSize = dt.Rows.Count;
                    sbc.BulkCopyTimeout = 180;
                    sbc.WriteToServer(dt);
                    transaction.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    LogMsg.WriteLog(ex.ToString());
                    transaction.Rollback();
                }
            }

            return false;
}

 

VB.Net批量插入

Protected SQLConString As String = ConfigurationManager.ConnectionStrings("connectionstring").ToString()
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim startDate As DateTime = DateTime.Now
        'batchInsert()  '1198.6272毫秒
        OneInsert()  '604554.6833毫秒
        Dim endDate As DateTime = DateTime.Now
        Response.Write((endDate - startDate).TotalMilliseconds)

End Sub

'单条数据添加
Private Sub OneInsert()
        Using conn As New SqlClient.SqlConnection(SQLConString)
            Try
                conn.Open()
                Dim cmd As New SqlClient.SqlCommand
                cmd.Connection = conn
                cmd.CommandType = CommandType.Text
                For index = 1 To 100000
                    cmd.CommandText = "insert demo(Time,Name,Has,Age)values('" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "','第一个',1,18)"
                    cmd.ExecuteNonQuery()
                Next
            Catch ex As Exception

            End Try
        End Using
End Sub

'一次性批量添加
Private Sub batchInsert()
        Dim dt As New DataTable("demo")
        dt.Columns.Add("ID", GetType(Integer)) '虽然ID为自增字段，但仍然要添加列，否则会报异常“来自数据源的 String 类型的给定值不能转换为指定目标列的类型 datetime。”
        dt.Columns.Add("Time", GetType(DateTime))
        dt.Columns.Add("Name", GetType(String))
        dt.Columns.Add("Has", GetType(Integer))
        dt.Columns.Add("Age", GetType(Integer))


        For i = 1 To 100000
            Dim row As DataRow = dt.NewRow()
            row("Time") = DateTime.Now
            row("Name") = "第一个"
            row("Has") = 1
            row("Age") = 18
            dt.Rows.Add(row)
        Next

        Using conn As New SqlClient.SqlConnection(SQLConString)
            Dim transaction As SqlClient.SqlTransaction
            Try
                conn.Open()
                transaction = conn.BeginTransaction()
                Dim sbc As New SqlClient.SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, transaction)
                sbc.DestinationTableName = dt.TableName
                sbc.BatchSize = dt.Rows.Count
                sbc.BulkCopyTimeout = 180
                sbc.WriteToServer(dt)
                transaction.Commit()
            Catch ex As Exception
                transaction.Rollback()
            End Try
        End Using
End Sub

 

效率对比

通过上面的VB.NET的两种添加方式对比，同样是一次性添加10w数据，以下是他们的执行时间：

（时间单位：毫秒）

SqlBulkCopy：1198.6272≈1秒

for insert：604554.6833≈10分钟

 

使用中遇到的小问题（Q&A）

问题1：从bcp客户端收到一个对Colid 24 无效的列长度

原因：第24列的参数长度大于数据库列定义长度。

 

问题2：报异常“来自数据源的 String 类型的给定值不能转换为指定目标列的类型 datetime。”

原因： 没有添加自增列。