主页 > 知识库 > sql下三种批量插入数据的方法

sql下三种批量插入数据的方法

热门标签:呼叫中心市场需求 百度竞价排名 地方门户网站 铁路电话系统 AI电销 Linux服务器 网站排名优化 服务外包

本文将介绍三种批量插入数据的方法。第一种方法是使用循环语句逐个将数据项插入到数据库中;第二种方法使用的是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,'')

您可能感兴趣的文章:
  • SQLServer 批量插入数据的两种方法
  • SQL批量插入数据几种方案的性能详细对比
  • 用SQL批量插入数据的代码
  • 用SQL批量插入数据的存储过程
  • sql server中批量插入与更新两种解决方案分享(存储过程)
  • sql server中批量插入与更新两种解决方案分享(asp.net)
  • mssql2008 自定义表类型实现(批量插入或者修改)
  • 关于sql server批量插入和更新的两种解决方案
  • 用一条mysql语句插入多条数据
  • SQL Server中数据行批量插入脚本的存储实现
  • C#实现SQL批量插入数据到表的方法
  • CI框架AR操作(数组形式)实现插入多条sql数据的方法
  • SQL Server使用一个语句块批量插入多条记录的三种方法

标签:衡水 湖南 湘潭 铜川 兰州 仙桃 崇左 黄山

巨人网络通讯声明:本文标题《sql下三种批量插入数据的方法》,本文关键词  ;如发现本文内容存在版权问题,烦请提供相关信息告之我们,我们将及时沟通与处理。本站内容系统采集于网络,涉及言论、版权与本站无关。
  • 相关文章
  • 收缩
    • 微信客服
    • 微信二维码
    • 电话咨询

    • 400-1100-266