SqlServer批量插入（SqlBulkCopy、表值参数）

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

之前做项目需要用到数据库的批量插入，于是就研究了一下，现在做个总结。

创建了一个用来测试的Student表：

CREATE TABLE [dbo].[Student](
[ID] [int] PRIMARY KEY NOT NULL,
[Num] [varchar](10) NULL,
[Name] [nvarchar](64) NULL,
[Age] [int] NULL
)

一、SqlBulkCopy类：使用数据库BCP协议进行数据的批量复制，每一批的数量大约800条。

/// <summary>
/// 批量插入SqlBulkCopy
/// </summary>
/// <param name="dt"></param>
/// <param name="tableName">表名</param>
public static void BatchInsertBySqlBulkCopy(DataTable dt, string tableName)
{
using (SqlBulkCopy sbc = new SqlBulkCopy(connString))
{
sbc.BatchSize = dt.Rows.Count;
sbc.BulkCopyTimeout = ;
sbc.DestinationTableName = tableName;
for (int i = ; i < dt.Columns.Count; i++)
{
sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, i);
}
//全部写入数据库
sbc.WriteToServer(dt);
}
}

5万条数据插入花了2秒的时间：

二、表值参数：也叫表变量参数，使用用户定义的表类型来声明，简单理解就是可以把一个表当做参数传递。

CREATE TYPE [dbo].[mytb_student] AS TABLE(
[ID] [int] NOT NULL,
[Num] [varchar](10) NULL,
[Name] [nvarchar](64) NULL,
[Age] [int] NULL
)

/// <summary>
/// 批量插入使用表值参数
/// </summary>
/// <param name="dt"></param>
public static void BatchInsertByTableValue(DataTable dt, string sqlText)
{
using (SqlConnection sqlConn = new SqlConnection(connString))
{
using (SqlCommand sqlCmd = new SqlCommand(sqlText, sqlConn))
{
//把DataTable当做参数传入
SqlParameter sqlPar = sqlCmd.Parameters.AddWithValue("@dt", dt);
//指定表值参数中包含的构造数据的特殊数据类型。
sqlPar.SqlDbType = SqlDbType.Structured;
sqlPar.TypeName = "dbo.mytb_student";//表值参数名称
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
}
}
}

同样插入5万条数据，也是花了2秒的时间。

总结：SqlServer数据库批量插入除了使用SqlBulkCopy和表值参数，还可以使用SqlDataAdapter的Update方法，经过本人测试，在数据量越大的情况下，使用SqlBulkCopy的性能是最好的。

三、在SqlBulkCopy和表值参数进行批量插入时，DataTable列的赋值顺序必须和DB中的表类型定义的字段顺序一致。

IF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = 'type_im_check_detail' AND is_user_defined = 1)
BEGIN
CREATE TYPE type_im_check_detail AS TABLE
(
-- 字段定义顺序
[sheet_no] [varchar](18) NOT NULL,
[item_no] [varchar](40) NOT NULL,
check_date DATETIME null,
in_price NUMERIC(16,4) null,
sale_price NUMERIC(16,4) null,
real_qty NUMERIC(16,4) null,
recheck_qty NUMERIC(16,4) null,
memo NVARCHAR(100) NULL,
item_barcode VARCHAR(40) null,
row_id NUMERIC(16,4) null,
produce_date DATETIME null,
valid_date DATETIME null,
stock_qty NUMERIC(16,4) null
)
END
GOIF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = 'type_im_sheet_barcode_qty' AND is_user_defined = 1)
BEGIN
CREATE TYPE type_im_sheet_barcode_qty AS TABLE
(
-- 字段定义顺序
trans_no VARCHAR(2) NOT NULL,
[sheet_no] [varchar](18) NOT NULL,
line_no INT NOT NULL,
[item_no] [varchar](30) NOT NULL,
item_barcode VARCHAR(38) NOT NULL,
qty NUMERIC(16,4) NULL,
qty2 NUMERIC(16,4) NULL,
qty3 NUMERIC(16,4) NULL
)
END
GOIF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('pr_insert_im_check_detail') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE dbo.pr_insert_im_check_detail
END
GO
CREATE PROCEDURE dbo.pr_insert_im_check_detail
(
@detail type_im_check_detail READONLY,
@barcodeDetail type_im_sheet_barcode_qty READONLY
)
AS
BEGIN
SET XACT_ABORT ON
BEGIN TRANSACTION INSERT t_im_check_detail (sheet_no, item_no, check_date, in_price, sale_price, real_qty, recheck_qty, memo, item_barcode, row_id, produce_date, valid_date,stock_qty)
SELECT sheet_no,item_no,check_date,in_price,sale_price,real_qty,recheck_qty,memo,item_barcode,row_id,produce_date,valid_date,stock_qty
FROM @detail INSERT t_im_sheet_barcode_qty (trans_no,sheet_no, item_no,line_no,item_barcode, qty)
SELECT trans_no,sheet_no, item_no,line_no,item_barcode, qty
FROM @barcodeDetail COMMIT TRANSACTION
SET XACT_ABORT OFF
END
GO

// DataTable列定义顺序
private DataTable GetNewDetailTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("sheet_no");
dt.Columns.Add("item_no");
dt.Columns.Add("check_date");
dt.Columns.Add("in_price");
dt.Columns.Add("sale_price");
dt.Columns.Add("real_qty");
dt.Columns.Add("recheck_qty");
dt.Columns.Add("memo");
dt.Columns.Add("item_barcode");
dt.Columns.Add("row_id");
dt.Columns.Add("produce_date");
dt.Columns.Add("valid_date");
dt.Columns.Add("stock_qty"); return dt;
} private DataTable GetNewBarcodeDetailTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("trans_no");
dt.Columns.Add("sheet_no");
dt.Columns.Add("line_no");
dt.Columns.Add("item_no");
dt.Columns.Add("item_barcode");
dt.Columns.Add("qty");
dt.Columns.Add("qty2");
dt.Columns.Add("qty3"); return dt;
}

#region init table
DataTable dt = GetNewDetailTable();
foreach (var item in details)
{
// datatable赋值顺序
DataRow row = dt.NewRow();
row["sheet_no"] = item.sheet_no;
row["item_no"] = item.item_no;
row["check_date"] = item.check_date;
row["in_price"] = item.in_price;
row["sale_price"] = item.sale_price;
row["real_qty"] = item.real_qty;
row["recheck_qty"] = item.recheck_qty;
row["memo"] = item.memo;
row["item_barcode"] = item.item_barcode;
row["row_id"] = item.row_id;
row["produce_date"] = item.produce_date;
row["valid_date"] = item.valid_date;
row["stock_qty"] = item.stock_qty; dt.Rows.Add(row);
}var dtBarcode = GetNewBarcodeDetailTable();
foreach (var item in barcodeDetail)
{
//datatable列赋值顺序
DataRow row = dtBarcode.NewRow();
row["trans_no"] = "CR";
row["sheet_no"] = item.sheet_no;
row["line_no"] = ;
row["item_no"] = item.item_no;
row["item_barcode"] = item.item_barcode;
row["qty"] = item.qty;
row["qty2"] = 0.00;
row["qty3"] = 0.00; dtBarcode.Rows.Add(row);
}
#endregionSqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@detail", SqlDbType.Structured) { Value = dt, TypeName = "type_im_check_detail" },
new SqlParameter("@barcodeDetail", SqlDbType.Structured) { Value = dtBarcode, TypeName = "type_im_sheet_barcode_qty" }
};
Repository.Database.ExecuteSqlCommand("pr_insert_im_check_detail @detail,@barcodeDetail", paras);