SQL Server批量插入千条数据咋办 思路是什么

这篇文章给大家介绍了“SQL Server批量插入千条数据怎么做，思路是什么”的相关知识，讲解详细，步骤过程清晰，有一定的借鉴学习价值，因此分享给大家做个参考，感兴趣的朋友接下来一起跟随小编看看吧。

在SQL Server 中插入一条数据使用Insert语句，但是如果想要批量插入一堆数据的话，循环使用Insert不仅效率低，而且会导致SQL一系统性能问题。下面介绍SQL Server支持的两种批量数据插入方法：Bulk和表值参数(Table-Valued Parameters)，高效插入数据。

新建数据库：

--Create DataBase

create database BulkTestDB;

go

use BulkTestDB;

go

--Create Table

Create table BulkTestTable(

Id int primary key,

UserName nvarchar(32),

Pwd varchar(16))

go

一.传统的INSERT方式

先看下传统的INSERT方式：一条一条的插入（性能消耗越来越大，速度越来越慢）

//使用简单的Insert方法一条条插入 [慢]

#region [ simpleInsert ]

static void simpleInsert()

{

Console.WriteLine("使用简单的Insert方法一条条插入");

Stopwatch sw = new Stopwatch();

SqlConnection sqlconn = new SqlConnection("server=.;database=BulkTestDB;user=sa;password=123456;");

SqlCommand sqlcmd = new SqlCommand();

sqlcmd.CommandText = string.Format("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");

sqlcmd.Parameters.Add("@p0", SqlDbType.Int);

sqlcmd.Parameters.Add("@p1", SqlDbType.NVarChar);

sqlcmd.Parameters.Add("@p2", SqlDbType.NVarChar);

sqlcmd.CommandType = CommandType.Text;

sqlcmd.Connection = sqlconn;

sqlconn.Open();

try

{

//循环插入1000条数据，每次插入100条，插入10次。

for (int multiply = 0; multiply < 10; multiply++)

{

for (int count = multiply * 100; count < (multiply + 1) * 100; count++)

{

sqlcmd.Parameters["@p0"].Value = count;

sqlcmd.Parameters["@p1"].Value = string.Format("User-{0}", count * multiply);

sqlcmd.Parameters["@p2"].Value = string.Format("Pwd-{0}", count * multiply);

sw.Start();

sqlcmd.ExecuteNonQuery();

sw.Stop();

}

//每插入10万条数据后，显示此次插入所用时间

Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));

}

Console.ReadKey();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

}

#endregion

循环插入1000条数据，每次插入100条，插入10次，效率是越来越慢。

二.较快速的Bulk插入方式：

使用使用Bulk插入[ 较快 ]

//使用Bulk插入的情况 [ 较快 ]

#region [ 使用Bulk插入的情况 ]

static void BulkToDB(DataTable dt)

{

Stopwatch sw = new Stopwatch();

SqlConnection sqlconn = new SqlConnection("server=.;database=BulkTestDB;user=sa;password=123456;");

SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlconn);

bulkCopy.DestinationTableName = "BulkTestTable";

bulkCopy.BatchSize = dt.Rows.Count;

try

{

sqlconn.Open();

if (dt != null && dt.Rows.Count != 0)

{

bulkCopy.WriteToServer(dt);

}

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

finally

{

sqlconn.Close();

if (bulkCopy != null)

{

bulkCopy.Close();

}

}

}

static DataTable GetTableSchema()

{

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;

}

static void BulkInsert()

{

Console.WriteLine("使用简单的Bulk插入的情况");

Stopwatch sw = new Stopwatch();

for (int multiply = 0; multiply < 10; multiply++)

{

DataTable dt = GetTableSchema();

for (int count = multiply * 100; count < (multiply + 1) * 100; count++)

{

DataRow r = dt.NewRow();

r[0] = count;

r[1] = string.Format("User-{0}", count * multiply);

r[2] = string.Format("Pwd-{0}", count * multiply);

dt.Rows.Add(r);

}

sw.Start();

BulkToDB(dt);

sw.Stop();

Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));

}

}

#endregion

循环插入1000条数据，每次插入100条，插入10次，效率快了很多。

三.使用简称TVPs插入数据

打开sqlserrver，执行以下脚本：

--Create Table Valued

CREATE TYPE BulkUdt AS TABLE

(Id int,

UserName nvarchar(32),

Pwd varchar(16))

成功后在数据库中发现多了BulkUdt的缓存表。

使用简称TVPs插入数据

//使用简称TVPs插入数据 [最快]

#region [ 使用简称TVPs插入数据 ]

static void TbaleValuedToDB(DataTable dt)

{

Stopwatch sw = new Stopwatch();

SqlConnection sqlconn = new SqlConnection("server=.;database=BulkTestDB;user=sa;password=123456;");

const string TSqlStatement =

"insert into BulkTestTable (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.BulkUdt";

try

{

sqlconn.Open();

if (dt != null && dt.Rows.Count != 0)

{

cmd.ExecuteNonQuery();

}

}

catch (Exception ex)

{

Console.WriteLine("error>" + ex.Message);

}

finally

{

sqlconn.Close();

}

}

static void TVPsInsert()

{

Console.WriteLine("使用简称TVPs插入数据");

Stopwatch sw = new Stopwatch();

for (int multiply = 0; multiply < 10; multiply++)

{

DataTable dt = GetTableSchema();

for (int count = multiply * 100; count < (multiply + 1) * 100; count++)

{

DataRow r = dt.NewRow();

r[0] = count;

r[1] = string.Format("User-{0}", count * multiply);

r[2] = string.Format("Pwd-{0}", count * multiply);

dt.Rows.Add(r);

}

sw.Start();

TbaleValuedToDB(dt);

sw.Stop();

Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));

}

Console.ReadLine();

}

#endregion

循环插入1000条数据，每次插入100条，插入10次，效率是越来越慢，后面测试，将每次插入的数据量增大，会更大的体现TPVS插入的效率。

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!