本文將介紹三種批量插入數(shù)據(jù)的方法。第一種方法是使用循環(huán)語句逐個將數(shù)據(jù)項插入到數(shù)據(jù)庫中；第二種方法使用的是SqlBulkCopy，使您可以用其他源的數(shù)據(jù)有效批量加載 SQL Server 表；第三種使用的方法是sql server中的表值參數(shù)方法，表值參數(shù)是 SQL Server 2008 中的新參數(shù)類型。表值參數(shù)是使用用戶定義的表類型來聲明的。使用表值參數(shù)，可以不必創(chuàng)建臨時表或許多參數(shù)，即可向 Transact-SQL 語句或例程（如存儲過程或函數(shù)）發(fā)送多行數(shù)據(jù)。
代碼示例：
此例子為控制臺輸出程序，有兩個類，一個為BulkData類，主要實現(xiàn)了表值參數(shù)和sqlbulkcopy是如何插入數(shù)據(jù)的，一個類為Repository，一個app.config配置文件。所用數(shù)據(jù)庫為sql server 2012。
建庫語句：
打開
--Create DataBase
use master
go
if exists(select * from master.sys.sysdatabases where name=N'BulkDB')
drop database BulkDB
create database BulkDB;
go
--Create Table
use BulkDB
go
if exists(select * from sys.objects where object_id=OBJECT_ID(N'[dbo].[BulkTable]') and type in(N'U'))
drop table [dbo].BulkTable
Create table BulkTable(
Id int primary key,
UserName nvarchar(32),
Pwd varchar(16))
go
--Create Table Valued
use BulkDB
go
if exists
(
select * from sys.types st
join sys.schemas ss
on st.schema_id=ss.schema_id
where st.name=N'[BulkType]' and ss.name=N'dbo'
)
drop type [dbo].[BulkType]
go
create type [dbo].[BulkType] as table
(
Id int,
UserName nvarchar(32),
Pwd varchar(16)
)
go
select * from dbo.BulkTable
BulkData.cs
打開
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace BulkData
{
class BulkData
{
public static void TableValuedToDB(DataTable dt)
{
SqlConnection sqlConn = new SqlConnection(
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
const string TSqlStatement =
"insert into BulkTable (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;
catParam.TypeName = "dbo.BulkType";
try
{
sqlConn.Open();
if (dt != null && dt.Rows.Count != 0)
{
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
}
public static DataTable GetTable()
{
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;
}
public static void BulkToDB(DataTable dt)
{
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
bulkCopy.DestinationTableName = "BulkTable";
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();
}
}
}
}
Repository.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
namespace BulkData
{
public class Repository
{
public static void UseSqlBulkCopyClass()
{
Stopwatch sw = new Stopwatch();
for (int outLayer = 0; outLayer < 10; outLayer++)
{
DataTable dt = BulkData.GetTable();
for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++)
{
DataRow r = dt.NewRow();
r[0] = count;
r[1] = string.Format("User-{0}", count * outLayer);
r[2] = string.Format("Password-{0}", count * outLayer);
dt.Rows.Add(r);
}
sw.Start();
BulkData.BulkToDB(dt);
sw.Stop();
Console.WriteLine(string.Format("{1} hundred thousand data elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds, outLayer + 1));
}
Console.ReadLine();
}
public static void UseTableValue()
{
Stopwatch sw = new Stopwatch();
for (int outLayer = 0; outLayer < 10; outLayer++)
{
DataTable dt = BulkData.GetTable();
for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++)
{
DataRow dataRow = dt.NewRow();
dataRow[0] = count;
dataRow[1] = string.Format("User-{0}", count * outLayer);
dataRow[2] = string.Format("Password-{0}", count * outLayer);
dt.Rows.Add(dataRow);
}
sw.Start();
BulkData.TableValuedToDB(dt);
sw.Stop();
Console.WriteLine(string.Format("{1} hundred thousand data elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds, outLayer + 1));
}
Console.ReadLine();
}
public static void UserNormalInsert()
{
Stopwatch sw = new Stopwatch();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
SqlCommand sqlComm = new SqlCommand();
sqlComm.CommandText = string.Format("insert into BulkTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");
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
{
for (int outLayer = 0; outLayer < 10; outLayer++)
{
for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++)
{
sqlComm.Parameters["@p0"].Value = count;
sqlComm.Parameters["@p1"].Value = string.Format("User-{0}", count * outLayer);
sqlComm.Parameters["@p2"].Value = string.Format("Password-{0}", count * outLayer);
sw.Start();
sqlComm.ExecuteNonQuery();
sw.Stop();
}
Console.WriteLine(string.Format("{1} hundred thousand data elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds, outLayer + 1));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
Console.ReadLine();
}
}
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="ConnStr"
connectionString="data source=.;Integrated Security=SSPI;Initial Catalog=BulkDB"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
namespace BulkData
{
class Program
{
static void Main(string[] args)
{
//Repository.UseSqlBulkCopyClass();
Repository.UseTableValue();
//Repository.UserNormalInsert();
}
}
}
三種方法分別插入100萬條數(shù)據(jù)所用的時間為：
循環(huán)語句所用時間：
sqlbulkcopy方法所用時間為：
表值參數(shù)所用時間為：
我不會告訴你有一種sql語法可以這么寫:
insert into SystemSet_tbl (ss_guid,ss_type,ss_comment) values ('00000000-0000-0000-0000-000000000007',1,''),('00000000-0000-0000-0000-000000000008',1,'')
