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