SQLServer批量插入数据的两种方法

2023年6月21日发(作者：)

SQLServer批量插⼊数据的两种⽅法

SQLServer 批量插⼊数据的两种⽅法2009-07-27 19:31在SQL Server 中插⼊⼀条数据使⽤Insert语句，但是如果想要批量插⼊⼀堆数据的话，循环使⽤Insert不仅效率低，⽽且会导致SQL⼀系统性能问题。下⾯介绍SQL Server⽀持的两种批量数据插⼊⽅法：Bulk和表值参数(Table-Valued Parameters)。 运⾏下⾯的脚本，建⽴测试数据库和表值参数。代码如下:--Create DataBase

create database BulkTestDB;

go

use BulkTestDB;

go

--Create Table

Create table BulkTestTable(

Id int primary key,

UserName nvarchar(32),

Pwd varchar(16))

go

--Create Table Valued

CREATE TYPE BulkUdt AS TABLE

(Id int,

UserName nvarchar(32),

Pwd varchar(16))下⾯我们使⽤最简单的Insert语句来插⼊100万条数据，代码如下：代码如下:Stopwatch sw = new Stopwatch();

SqlConnection sqlConn = new SqlConnection(

tionStrings["ConnStr"].ConnectionString);//连接数据库

SqlCommand sqlComm = new SqlCommand(); SqlCommand sqlComm = new SqlCommand();

dText = ("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");//参数化SQL

("@p0", );

("@p1", ar);

("@p2", r);

dType = ;

tion = sqlConn;

();

try

{

//循环插⼊100万条数据，每次插⼊10万条，插⼊10次。

for (int multiply = 0; multiply < 10; multiply++)

{

for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)

{

ters["@p0"].Value = count;

ters["@p1"].Value = ("User-{0}", count * multiply);

ters["@p2"].Value = ("Pwd-{0}", count * multiply);

();

eNonQuery();

();

}

//每插⼊10万条数据后，显⽰此次插⼊所⽤时间

ine(("Elapsed Time is {0} Milliseconds", dMilliseconds));

}

}

catch (Exception ex)

{

throw ex;

}

finally

{

();

}

ne();耗时图如下：

由于运⾏过慢，才插⼊10万条就耗时72390 milliseconds，所以我就⼿动强⾏停⽌了。

下⾯看⼀下使⽤Bulk插⼊的情况：

bulk⽅法主要思想是通过在客户端把数据都缓存在Table中，然后利⽤SqlBulkCopy⼀次性把Table中的数据插⼊到数据库

代码如下：复制代码 代码如下:public static void BulkToDB(DataTable dt)

{

SqlConnection sqlConn = new SqlConnection(

tionStrings["ConnStr"].ConnectionString);

SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);

ationTableName = "BulkTestTable";

ize = ;

try

{

();

if (dt != null && != 0)

oServer(dt);

} }

catch (Exception ex)

{

throw ex;

}

finally

{

();

if (bulkCopy != null)

();

}

}

public static DataTable GetTableSchema()

{

DataTable dt = new DataTable();

ge(new DataColumn[]{

new DataColumn("Id",typeof(int)),

new DataColumn("UserName",typeof(string)),

new DataColumn("Pwd",typeof(string))});

return dt;

}

static void Main(string[] args)

{

Stopwatch sw = new Stopwatch();

for (int multiply = 0; multiply < 10; multiply++)

{

DataTable dt = leSchema();

for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)

{

DataRow r = ();

r[0] = count;

r[1] = ("User-{0}", count * multiply);

r[2] = ("Pwd-{0}", count * multiply);

(r);

}

();

DB(dt);

();

ine(("Elapsed Time is {0} Milliseconds", dMilliseconds));

}

ne();

}耗时图如下：

可见，使⽤Bulk后，效率和性能明显上升。使⽤Insert插⼊10万数据耗时72390，⽽现在使⽤Bulk插⼊100万数据才耗时17583。

最后再看看使⽤表值参数的效率，会另你⼤为惊讶的。

表值参数是SQL Server 2008新特性，简称TVPs。对于表值参数不熟悉的朋友，可以参考最新的book online，我也会另外写⼀篇关于表值参数的博客，不过此次不对表值参数的概念做过多的介绍。⾔归正传，看代码：代码如下:public static void TableValuedToDB(DataTable dt)

{

SqlConnection sqlConn = new SqlConnection(

tionStrings["ConnStr"].ConnectionString);

const string TSqlStatement =

"insert into BulkTestTable (Id,UserName,Pwd)" +

" SELECT , me," + " FROM @NewBulkTestTvp AS nc";

SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);

SqlParameter catParam = hValue("@NewBulkTestTvp", dt);

ype = ured;

//表值参数的名字叫BulkUdt，在上⾯的建⽴测试环境的SQL中有。

me = "t";

try

{

();

if (dt != null && != 0)

{

eNonQuery();

}

}

catch (Exception ex)

{

throw ex;

}

finally

{

();

}

}

public static DataTable GetTableSchema()

{

DataTable dt = new DataTable();

ge(new DataColumn[]{

new DataColumn("Id",typeof(int)),

new DataColumn("UserName",typeof(string)),

new DataColumn("Pwd",typeof(string))});

return dt;

}

static void Main(string[] args)

{

Stopwatch sw = new Stopwatch();

for (int multiply = 0; multiply < 10; multiply++)

{

DataTable dt = leSchema();

for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)

{

DataRow r = ();

r[0] = count;

r[1] = ("User-{0}", count * multiply);

r[2] = ("Pwd-{0}", count * multiply);

(r);

}

();

aluedToDB(dt);

();

ine(("Elapsed Time is {0} Milliseconds", dMilliseconds));

}

ne();

}耗时图如下： ⽐Bulk还快5秒。此⽂原创⾃CSDN TJVictor

2023年6月21日发(作者：)

SQLServer批量插⼊数据的两种⽅法

SQLServer 批量插⼊数据的两种⽅法2009-07-27 19:31在SQL Server 中插⼊⼀条数据使⽤Insert语句，但是如果想要批量插⼊⼀堆数据的话，循环使⽤Insert不仅效率低，⽽且会导致SQL⼀系统性能问题。下⾯介绍SQL Server⽀持的两种批量数据插⼊⽅法：Bulk和表值参数(Table-Valued Parameters)。 运⾏下⾯的脚本，建⽴测试数据库和表值参数。代码如下:--Create DataBase

create database BulkTestDB;

go

use BulkTestDB;

go

--Create Table

Create table BulkTestTable(

Id int primary key,

UserName nvarchar(32),

Pwd varchar(16))

go

--Create Table Valued

CREATE TYPE BulkUdt AS TABLE

(Id int,

UserName nvarchar(32),

Pwd varchar(16))下⾯我们使⽤最简单的Insert语句来插⼊100万条数据，代码如下：代码如下:Stopwatch sw = new Stopwatch();

SqlConnection sqlConn = new SqlConnection(

tionStrings["ConnStr"].ConnectionString);//连接数据库

SqlCommand sqlComm = new SqlCommand(); SqlCommand sqlComm = new SqlCommand();

dText = ("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");//参数化SQL

("@p0", );

("@p1", ar);

("@p2", r);

dType = ;

tion = sqlConn;

();

try

{

//循环插⼊100万条数据，每次插⼊10万条，插⼊10次。

for (int multiply = 0; multiply < 10; multiply++)

{

for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)

{

ters["@p0"].Value = count;

ters["@p1"].Value = ("User-{0}", count * multiply);

ters["@p2"].Value = ("Pwd-{0}", count * multiply);

();

eNonQuery();

();

}

//每插⼊10万条数据后，显⽰此次插⼊所⽤时间

ine(("Elapsed Time is {0} Milliseconds", dMilliseconds));

}

}

catch (Exception ex)

{

throw ex;

}

finally

{

();

}

ne();耗时图如下：

由于运⾏过慢，才插⼊10万条就耗时72390 milliseconds，所以我就⼿动强⾏停⽌了。

下⾯看⼀下使⽤Bulk插⼊的情况：

bulk⽅法主要思想是通过在客户端把数据都缓存在Table中，然后利⽤SqlBulkCopy⼀次性把Table中的数据插⼊到数据库

代码如下：复制代码 代码如下:public static void BulkToDB(DataTable dt)

{

SqlConnection sqlConn = new SqlConnection(

tionStrings["ConnStr"].ConnectionString);

SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);

ationTableName = "BulkTestTable";

ize = ;

try

{

();

if (dt != null && != 0)

oServer(dt);

} }

catch (Exception ex)

{

throw ex;

}

finally

{

();

if (bulkCopy != null)

();

}

}

public static DataTable GetTableSchema()

{

DataTable dt = new DataTable();

ge(new DataColumn[]{

new DataColumn("Id",typeof(int)),

new DataColumn("UserName",typeof(string)),

new DataColumn("Pwd",typeof(string))});

return dt;

}

static void Main(string[] args)

{

Stopwatch sw = new Stopwatch();

for (int multiply = 0; multiply < 10; multiply++)

{

DataTable dt = leSchema();

for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)

{

DataRow r = ();

r[0] = count;

r[1] = ("User-{0}", count * multiply);

r[2] = ("Pwd-{0}", count * multiply);

(r);

}

();

DB(dt);

();

ine(("Elapsed Time is {0} Milliseconds", dMilliseconds));

}

ne();

}耗时图如下：

可见，使⽤Bulk后，效率和性能明显上升。使⽤Insert插⼊10万数据耗时72390，⽽现在使⽤Bulk插⼊100万数据才耗时17583。

最后再看看使⽤表值参数的效率，会另你⼤为惊讶的。

表值参数是SQL Server 2008新特性，简称TVPs。对于表值参数不熟悉的朋友，可以参考最新的book online，我也会另外写⼀篇关于表值参数的博客，不过此次不对表值参数的概念做过多的介绍。⾔归正传，看代码：代码如下:public static void TableValuedToDB(DataTable dt)

{

SqlConnection sqlConn = new SqlConnection(

tionStrings["ConnStr"].ConnectionString);

const string TSqlStatement =

"insert into BulkTestTable (Id,UserName,Pwd)" +

" SELECT , me," + " FROM @NewBulkTestTvp AS nc";

SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);

SqlParameter catParam = hValue("@NewBulkTestTvp", dt);

ype = ured;

//表值参数的名字叫BulkUdt，在上⾯的建⽴测试环境的SQL中有。

me = "t";

try

{

();

if (dt != null && != 0)

{

eNonQuery();

}

}

catch (Exception ex)

{

throw ex;

}

finally

{

();

}

}

public static DataTable GetTableSchema()

{

DataTable dt = new DataTable();

ge(new DataColumn[]{

new DataColumn("Id",typeof(int)),

new DataColumn("UserName",typeof(string)),

new DataColumn("Pwd",typeof(string))});

return dt;

}

static void Main(string[] args)

{

Stopwatch sw = new Stopwatch();

for (int multiply = 0; multiply < 10; multiply++)

{

DataTable dt = leSchema();

for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)

{

DataRow r = ();

r[0] = count;

r[1] = ("User-{0}", count * multiply);

r[2] = ("Pwd-{0}", count * multiply);

(r);

}

();

aluedToDB(dt);

();

ine(("Elapsed Time is {0} Milliseconds", dMilliseconds));

}

ne();

}耗时图如下： ⽐Bulk还快5秒。此⽂原创⾃CSDN TJVictor