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

这篇文章主要为大家详细介绍了SQL Server sql下三种批量插入数据的方法，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

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

if exists(select * from sys.objects where object_id=OBJECT_ID(N'[dbo].[BulkTable]') and type in(N'U'))drop table [dbo].BulkTableCreate table BulkTable(Id int primary key,UserName nvarchar(32),Pwd varchar(16))go

--Create Table Valueduse BulkDBgo

if exists(select * from sys.types stjoin sys.schemas sson st.schema_id=ss.schema_idwhere 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 Server sql下三种批量插入数据的方法的内容就先介绍到这里，更多相关文章的可以留意