sql下三种批量插入数据的方法
本文将介绍三种批量插入数据的方法。第一种方法是使用循环语句逐个将数据项插入到数据库中；第二种方法使用的是sqlbulkcopy，使您可以用其他源的数据有效批量加载 sql server 表；第三种使用的方法是sql server中的表值参数方法，表值参数是 sql server 2008 中的新参数类型。表值参数是使用用户定义的表类型来声明的。使用表值参数，可以不必创建临时表或许多参数，即可向 transact-sql 语句或例程（如存储过程或函数）发送多行数据。
代码示例：
此例子为控制台输出程序，有两个类，一个为bulkdata类，主要实现了表值参数和sqlbulkcopy是如何插入数据的，一个类为repository，一个app.config配置文件。所用数据库为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万条数据所用的时间为：
循环语句所用时间：
sqlbulkcopy方法所用时间为：
表值参数所用时间为：
我不会告诉你有一种sql语法可以这么写:
insert into systemset_tbl (ss_guid,ss_type,ss_comment) values ('00000000-0000-0000-0000-000000000007',1,''),('00000000-0000-0000-0000-000000000008',1,'')