SQLServer 批量插入數(shù)據(jù)的兩種方法

更新時(shí)間：2009年07月22日 08:33:18 作者：

在SQL Server 中插入一條數(shù)據(jù)使用Insert語句，但是如果想要批量插入一堆數(shù)據(jù)的話，循環(huán)使用Insert不僅效率低，而且會(huì)導(dǎo)致SQL一系統(tǒng)性能問題。下面介紹SQL Server支持的兩種批量數(shù)據(jù)插入方法：Bulk和表值參數(shù)(Table-Valued Parameters)。

運(yùn)行下面的腳本，建立測(cè)試數(shù)據(jù)庫和表值參數(shù)。

復(fù)制代碼代碼如下:

--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))

下面我們使用最簡(jiǎn)單的Insert語句來插入100萬條數(shù)據(jù)，代碼如下：

復(fù)制代碼代碼如下:

Stopwatch sw = new Stopwatch(); 

SqlConnection sqlConn = new SqlConnection( 
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);//連接數(shù)據(jù)庫 

SqlCommand sqlComm = new SqlCommand(); 
sqlComm.CommandText = string.Format("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");//參數(shù)化SQL 
sqlComm.Parameters.Add("@p0", SqlDbType.Int); 
sqlComm.Parameters.Add("@p1", SqlDbType.NVarChar); 
sqlComm.Parameters.Add("@p2", SqlDbType.VarChar); 
sqlComm.CommandType = CommandType.Text; 
sqlComm.Connection = sqlConn; 
sqlConn.Open(); 
try 
{ 
//循環(huán)插入100萬條數(shù)據(jù)，每次插入10萬條，插入10次。 
for (int multiply = 0; multiply < 10; multiply++) 
{ 
for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++) 
{ 

sqlComm.Parameters["@p0"].Value = count; 
sqlComm.Parameters["@p1"].Value = string.Format("User-{0}", count * multiply); 
sqlComm.Parameters["@p2"].Value = string.Format("Pwd-{0}", count * multiply); 
sw.Start(); 
sqlComm.ExecuteNonQuery(); 
sw.Stop(); 
} 
//每插入10萬條數(shù)據(jù)后，顯示此次插入所用時(shí)間 
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds)); 
} 
} 
catch (Exception ex) 
{ 
throw ex; 
} 
finally 
{ 
sqlConn.Close(); 
} 

Console.ReadLine();

耗時(shí)圖如下：

使用Insert語句插入10萬數(shù)據(jù)的耗時(shí)圖

由于運(yùn)行過慢，才插入10萬條就耗時(shí)72390 milliseconds，所以我就手動(dòng)強(qiáng)行停止了。

下面看一下使用Bulk插入的情況：

bulk方法主要思想是通過在客戶端把數(shù)據(jù)都緩存在Table中，然后利用SqlBulkCopy一次性把Table中的數(shù)據(jù)插入到數(shù)據(jù)庫

代碼如下：

復(fù)制代碼代碼如下:

public static void BulkToDB(DataTable dt) 
{ 
SqlConnection sqlConn = new SqlConnection( 
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString); 
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn); 
bulkCopy.DestinationTableName = "BulkTestTable"; 
bulkCopy.BatchSize = dt.Rows.Count; 

try 
{ 
sqlConn.Open(); 
    if (dt != null && dt.Rows.Count != 0) 
    bulkCopy.WriteToServer(dt); 
} 
catch (Exception ex) 
{ 
throw ex; 
} 
finally 
{ 
sqlConn.Close(); 
if (bulkCopy != null) 
bulkCopy.Close(); 
} 
} 

public static DataTable GetTableSchema() 
{ 
DataTable dt = new DataTable(); 
dt.Columns.AddRange(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 = Bulk.GetTableSchema(); 
for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++) 
{ 
DataRow r = dt.NewRow(); 
r[0] = count; 
r[1] = string.Format("User-{0}", count * multiply); 
r[2] = string.Format("Pwd-{0}", count * multiply); 
dt.Rows.Add(r); 
} 
sw.Start(); 
Bulk.BulkToDB(dt); 
sw.Stop(); 
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds)); 
} 

Console.ReadLine(); 
}

耗時(shí)圖如下：
使用Bulk插入100萬數(shù)據(jù)的耗時(shí)圖

可見，使用Bulk后，效率和性能明顯上升。使用Insert插入10萬數(shù)據(jù)耗時(shí)72390，而現(xiàn)在使用Bulk插入100萬數(shù)據(jù)才耗時(shí)17583。

最后再看看使用表值參數(shù)的效率，會(huì)另你大為驚訝的。

表值參數(shù)是SQL Server 2008新特性，簡(jiǎn)稱TVPs。對(duì)于表值參數(shù)不熟悉的朋友，可以參考最新的book online，我也會(huì)另外寫一篇關(guān)于表值參數(shù)的博客，不過此次不對(duì)表值參數(shù)的概念做過多的介紹。言歸正傳，看代碼：

復(fù)制代碼代碼如下:

public static void TableValuedToDB(DataTable dt) 
{ 
SqlConnection sqlConn = new SqlConnection( 
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString); 
const string TSqlStatement = 
"insert into BulkTestTable (Id,UserName,Pwd)" + 
" SELECT nc.Id, nc.UserName,nc.Pwd" + 
" FROM @NewBulkTestTvp AS nc"; 
SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn); 
SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt); 
catParam.SqlDbType = SqlDbType.Structured; 
//表值參數(shù)的名字叫BulkUdt，在上面的建立測(cè)試環(huán)境的SQL中有。 
catParam.TypeName = "dbo.BulkUdt"; 
try 
{ 
sqlConn.Open(); 
if (dt != null && dt.Rows.Count != 0) 
{ 
cmd.ExecuteNonQuery(); 
} 
} 
catch (Exception ex) 
{ 
throw ex; 
} 
finally 
{ 
sqlConn.Close(); 
} 
} 

public static DataTable GetTableSchema() 
{ 
DataTable dt = new DataTable(); 
dt.Columns.AddRange(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 = TableValued.GetTableSchema(); 
for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++) 
{ 
DataRow r = dt.NewRow(); 
r[0] = count; 
r[1] = string.Format("User-{0}", count * multiply); 
r[2] = string.Format("Pwd-{0}", count * multiply); 
dt.Rows.Add(r); 
} 
sw.Start(); 
TableValued.TableValuedToDB(dt); 
sw.Stop(); 
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds)); 
} 

Console.ReadLine(); 
}