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

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐