当前位置: 首页 > 图文教程 > 数据库 > MSSQL > SQLServer 批量插入数据的两种方法

MSSQL
SQL Server中删除重复数据的几个方法
SQL Server 基础概念集
SQL Server导入导出
SQL SERVER实用技巧
sql server 常用函数
如何维护SQL Server的“交易日志”
SQL Server数据备份处理过程探讨
JSP中tomcat的SQL Server2000数据库连接池的配置
SQL Server服务器安装剖析
SQL Server 2005数据加密技术应用研究
SQL Server数据库的备份和恢复措施
SQL Server 2005性能测试实践
Jboss下MS SQL Server配置指导
Oracle和SQL Server 追求完美还是讲求实用
SQL Server服务器安装剖析
SQL Server：Oracle与SQL Server事务处理的比较
SQL Server：利用配置文件实现SQL Server与Oralce访问类的转换
SQL Server：测试SQL Server业务规则链接方法
SQL Server ：SQL Server 2008高可用性解决方案优劣分析
SQLServer：数据库恢复

No. « 1 2 3 4 »

技术文章搜索

关键字

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

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2009-09-13   浏览: 102 ::
收藏到网摘: n/a

在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(
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);//连接数据库
SqlCommand sqlComm = new SqlCommand();
sqlComm.CommandText = string.Format("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");//参数化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
{
//循环插入100万条数据，每次插入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万条数据后，显示此次插入所用时间
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
Console.ReadLine();

耗时图如下：

使用Insert语句插入10万数据的耗时图

由于运行过慢，才插入10万条就耗时72390 milliseconds，所以我就手动强行停止了。
下面看一下使用Bulk插入的情况：
bulk方法主要思想是通过在客户端把数据都缓存在Table中，然后利用SqlBulkCopy一次性把Table中的数据插入到数据库
代码如下：

复制代码 代码如下:

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();
}

耗时图如下：
使用Bulk插入100万数据的耗时图
可见，使用Bulk后，效率和性能明显上升。使用Insert插入10万数据耗时72390，而现在使用Bulk插入100万数据才耗时17583。
最后再看看使用表值参数的效率，会另你大为惊讶的。
表值参数是SQL Server 2008新特性，简称TVPs。对于表值参数不熟悉的朋友，可以参考最新的book online，我也会另外写一篇关于表值参数的博客，不过此次不对表值参数的概念做过多的介绍。言归正传，看代码：
复制代码 代码如下:

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;
//表值参数的名字叫BulkUdt，在上面的建立测试环境的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();
}

耗时图如下：

使用表值参数插入100万数据的耗时图

比Bulk还快5秒。
此文原创自CSDN TJVictor