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

循环语句所用时间：

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

本文sql下三种批量插入数据的方法到此结束。在这个忧伤而明媚的三月，我从我单薄的青春里打马而过，穿过紫堇，穿过木棉，穿过时隐时现的悲喜和无常。小编再次感谢大家对我们的支持！