2023年6月21日发(作者：)

sql下三种批量插⼊数据的⽅法本⽂将介绍三种批量插⼊数据的⽅法。第⼀种⽅法是使⽤循环语句逐个将数据项插⼊到数据库中；第⼆种⽅法使⽤的是SqlBulkCopy，使您可以⽤其他源的数据有效批量加载 SQL Server 表；第三种使⽤的⽅法是sql server中的表值参数⽅法，表值参数是 SQL Server 2008 中的新参数类型。表值参数是使⽤⽤户定义的表类型来声明的。使⽤表值参数，可以不必创建临时表或许多参数，即可向 Transact-SQL 语句或例程（如存储过程或函数）发送多⾏数据。 代码⽰例： 此例⼦为控制台输出程序，有两个类，⼀个为BulkData类，主要实现了表值参数和sqlbulkcopy是如何插⼊数据的，⼀个类为Repository，⼀个配置⽂件。所⽤数据库为sql server 2012。 建库语句：复制代码 代码如下:打开

--Create DataBaseuse mastergoif exists(select * from abases where name=N'BulkDB')drop database BulkDBcreate database BulkDB;go--Create Tableuse BulkDBgoif exists(select * from s 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 BulkDBgoif exists(select * from st

join s ss

on _id=_idwhere =N'[BulkType]' and =N'dbo')drop type [dbo].[BulkType]gocreate type [dbo].[BulkType] as table ( Id int, UserName nvarchar(32), Pwd varchar(16) )goselect * from 复制代码 代码如下:打开

using System;using c;using ;using ;using ;using ent;using uration;namespace BulkData{ class BulkData { public static void TableValuedToDB(DataTable dt) { SqlConnection sqlConn = new SqlConnection( tionStrings["ConnStr"].ConnectionString); const string TSqlStatement = "insert into BulkTable (Id,UserName,Pwd)" + " SELECT , me," + " FROM @NewBulkTestTvp AS nc"; SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn); SqlParameter catParam = hValue("@NewBulkTestTvp", dt); ype = ured; me = "pe"; try { (); if (dt != null && != 0) { eNonQuery(); } } catch (Exception ex) { throw ex; } finally { (); } } public static DataTable GetTable() { DataTable dt = new DataTable(); ge(new DataColumn[]{new DataColumn("Id",typeof(int)),newDataColumn("UserName",typeof(string)),new DataColumn("Pwd",typeof(string))}); return dt; } public static void BulkToDB(DataTable dt) { SqlConnection sqlConn = newSqlConnection(tionStrings["ConnStr"].ConnectionString); SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn); ationTableName = "BulkTable"; ize = ; try { (); if (dt != null && != 0) oServer(dt); } catch (Exception ex) { throw ex; } finally { (); if (bulkCopy != null) (); } } }}复制代码 代码如下:using System;using c;using ;using ;using ;using ent;using uration;using stics;namespace BulkData{ public class Repository { public static void UseSqlBulkCopyClass() { Stopwatch sw = new Stopwatch(); for (int outLayer = 0; outLayer < 10; outLayer++) { DataTable dt = le(); for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++) { DataRow r = (); r[0] = count; r[1] = ("User-{0}", count * outLayer); r[2] = ("Password-{0}", count * outLayer); (r); } (); DB(dt); (); ine(("{1} hundred thousand data elapsed Time is {0} Milliseconds",dMilliseconds, outLayer + 1)); } ne(); } public static void UseTableValue() { Stopwatch sw = new Stopwatch(); for (int outLayer = 0; outLayer < 10; outLayer++) { DataTable dt = le(); for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++) { DataRow dataRow = (); dataRow[0] = count; dataRow[1] = ("User-{0}", count * outLayer); dataRow[2] = ("Password-{0}", count * outLayer); (dataRow); } (); aluedToDB(dt); (); ine(("{1} hundred thousand data elapsed Time is {0} Milliseconds",dMilliseconds, outLayer + 1)); } ne(); } public static void UserNormalInsert() { Stopwatch sw = new Stopwatch(); SqlConnection sqlConn = newSqlConnection(tionStrings["ConnStr"].ConnectionString); SqlCommand sqlComm = new SqlCommand(); dText = ("insert into BulkTable(Id,UserName,Pwd)values(@p0,@p1,@p2)"); ("@p0", ); ("@p1", ar); ("@p2", r); dType = ; tion = sqlConn; (); try { for (int outLayer = 0; outLayer < 10; outLayer++) { for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++) { ters["@p0"].Value = count; ters["@p1"].Value = ("User-{0}", count * outLayer); ters["@p2"].Value = ("Password-{0}", count * outLayer); (); eNonQuery(); (); } ine(("{1} hundred thousand data elapsed Time is {0} Milliseconds",dMilliseconds, outLayer + 1)); } } catch (Exception ex) { throw ex; } finally { (); } ne(); } }}复制代码 代码如下: 复制代码 代码如下:using System;using c;using ;using ;using ;using ent;using uration;using stics;namespace BulkData{ class Program {

static void Main(string[] args) { //BulkCopyClass(); leValue(); //rmalInsert(); }

}}三种⽅法分别插⼊100万条数据所⽤的时间为： 循环语句所⽤时间：

sqlbulkcopy⽅法所⽤时间为：

表值参数所⽤时间为：

我不会告诉你有⼀种sql语法可以这么写:复制代码 代码如下:insert into SystemSet_tbl (ss_guid,ss_type,ss_comment) values ('00000000-0000-0000-0007',1,''),('00000000-0000-0000-0008',1,'')

2023年6月21日发(作者：)

sql下三种批量插⼊数据的⽅法本⽂将介绍三种批量插⼊数据的⽅法。第⼀种⽅法是使⽤循环语句逐个将数据项插⼊到数据库中；第⼆种⽅法使⽤的是SqlBulkCopy，使您可以⽤其他源的数据有效批量加载 SQL Server 表；第三种使⽤的⽅法是sql server中的表值参数⽅法，表值参数是 SQL Server 2008 中的新参数类型。表值参数是使⽤⽤户定义的表类型来声明的。使⽤表值参数，可以不必创建临时表或许多参数，即可向 Transact-SQL 语句或例程（如存储过程或函数）发送多⾏数据。 代码⽰例： 此例⼦为控制台输出程序，有两个类，⼀个为BulkData类，主要实现了表值参数和sqlbulkcopy是如何插⼊数据的，⼀个类为Repository，⼀个配置⽂件。所⽤数据库为sql server 2012。 建库语句：复制代码 代码如下:打开

--Create DataBaseuse mastergoif exists(select * from abases where name=N'BulkDB')drop database BulkDBcreate database BulkDB;go--Create Tableuse BulkDBgoif exists(select * from s 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 BulkDBgoif exists(select * from st

join s ss

on _id=_idwhere =N'[BulkType]' and =N'dbo')drop type [dbo].[BulkType]gocreate type [dbo].[BulkType] as table ( Id int, UserName nvarchar(32), Pwd varchar(16) )goselect * from 复制代码 代码如下:打开

using System;using c;using ;using ;using ;using ent;using uration;namespace BulkData{ class BulkData { public static void TableValuedToDB(DataTable dt) { SqlConnection sqlConn = new SqlConnection( tionStrings["ConnStr"].ConnectionString); const string TSqlStatement = "insert into BulkTable (Id,UserName,Pwd)" + " SELECT , me," + " FROM @NewBulkTestTvp AS nc"; SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn); SqlParameter catParam = hValue("@NewBulkTestTvp", dt); ype = ured; me = "pe"; try { (); if (dt != null && != 0) { eNonQuery(); } } catch (Exception ex) { throw ex; } finally { (); } } public static DataTable GetTable() { DataTable dt = new DataTable(); ge(new DataColumn[]{new DataColumn("Id",typeof(int)),newDataColumn("UserName",typeof(string)),new DataColumn("Pwd",typeof(string))}); return dt; } public static void BulkToDB(DataTable dt) { SqlConnection sqlConn = newSqlConnection(tionStrings["ConnStr"].ConnectionString); SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn); ationTableName = "BulkTable"; ize = ; try { (); if (dt != null && != 0) oServer(dt); } catch (Exception ex) { throw ex; } finally { (); if (bulkCopy != null) (); } } }}复制代码 代码如下:using System;using c;using ;using ;using ;using ent;using uration;using stics;namespace BulkData{ public class Repository { public static void UseSqlBulkCopyClass() { Stopwatch sw = new Stopwatch(); for (int outLayer = 0; outLayer < 10; outLayer++) { DataTable dt = le(); for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++) { DataRow r = (); r[0] = count; r[1] = ("User-{0}", count * outLayer); r[2] = ("Password-{0}", count * outLayer); (r); } (); DB(dt); (); ine(("{1} hundred thousand data elapsed Time is {0} Milliseconds",dMilliseconds, outLayer + 1)); } ne(); } public static void UseTableValue() { Stopwatch sw = new Stopwatch(); for (int outLayer = 0; outLayer < 10; outLayer++) { DataTable dt = le(); for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++) { DataRow dataRow = (); dataRow[0] = count; dataRow[1] = ("User-{0}", count * outLayer); dataRow[2] = ("Password-{0}", count * outLayer); (dataRow); } (); aluedToDB(dt); (); ine(("{1} hundred thousand data elapsed Time is {0} Milliseconds",dMilliseconds, outLayer + 1)); } ne(); } public static void UserNormalInsert() { Stopwatch sw = new Stopwatch(); SqlConnection sqlConn = newSqlConnection(tionStrings["ConnStr"].ConnectionString); SqlCommand sqlComm = new SqlCommand(); dText = ("insert into BulkTable(Id,UserName,Pwd)values(@p0,@p1,@p2)"); ("@p0", ); ("@p1", ar); ("@p2", r); dType = ; tion = sqlConn; (); try { for (int outLayer = 0; outLayer < 10; outLayer++) { for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++) { ters["@p0"].Value = count; ters["@p1"].Value = ("User-{0}", count * outLayer); ters["@p2"].Value = ("Password-{0}", count * outLayer); (); eNonQuery(); (); } ine(("{1} hundred thousand data elapsed Time is {0} Milliseconds",dMilliseconds, outLayer + 1)); } } catch (Exception ex) { throw ex; } finally { (); } ne(); } }}复制代码 代码如下: 复制代码 代码如下:using System;using c;using ;using ;using ;using ent;using uration;using stics;namespace BulkData{ class Program {

static void Main(string[] args) { //BulkCopyClass(); leValue(); //rmalInsert(); }

}}三种⽅法分别插⼊100万条数据所⽤的时间为： 循环语句所⽤时间：

sqlbulkcopy⽅法所⽤时间为：

表值参数所⽤时间为：

我不会告诉你有⼀种sql语法可以这么写:复制代码 代码如下:insert into SystemSet_tbl (ss_guid,ss_type,ss_comment) values ('00000000-0000-0000-0007',1,''),('00000000-0000-0000-0008',1,'')