SQL Server批量插入數(shù)據(jù)案例詳解
發(fā)布日期：2022-01-31 08:14 | 文章來源：站長之家
在SQL Server 中插入一條數(shù)據(jù)使用Insert語句，但是如果想要批量插入一堆數(shù)據(jù)的話，循環(huán)使用Insert不僅效率低，而且會導(dǎo)致SQL一系統(tǒng)性能問題。下面介紹SQL Server支持的兩種批量數(shù)據(jù)插入方法：Bulk和表值參數(shù)(Table-Valued Parameters)，高效插入數(shù)據(jù)。
新建數(shù)據(jù)庫：
--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
一.傳統(tǒng)的INSERT方式
先看下傳統(tǒng)的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
{
//循環(huán)插入1000條數(shù)據(jù)，每次插入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萬條數(shù)據(jù)后，顯示此次插入所用時間
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
}
Console.ReadKey();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
#endregion
循環(huán)插入1000條數(shù)據(jù)，每次插入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
循環(huán)插入1000條數(shù)據(jù)，每次插入100條，插入10次，效率快了很多。
三.使用簡稱TVPs插入數(shù)據(jù)
打開sqlserrver，執(zhí)行以下腳本：
--Create Table Valued CREATE TYPE BulkUdt AS TABLE (Id int, UserName nvarchar(32), Pwd varchar(16))
成功后在數(shù)據(jù)庫中發(fā)現(xiàn)多了BulkUdt的緩存表。
使用簡稱TVPs插入數(shù)據(jù)
//使用簡稱TVPs插入數(shù)據(jù) [最快]
#region [ 使用簡稱TVPs插入數(shù)據(jù) ]
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插入數(shù)據(jù)");
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
循環(huán)插入1000條數(shù)據(jù)，每次插入100條，插入10次，效率是越來越慢，后面測試，將每次插入的數(shù)據(jù)量增大，會更大的體現(xiàn)TPVS插入的效率。
到此這篇關(guān)于SQL Server批量插入數(shù)據(jù)案例詳解的文章就介紹到這了,更多相關(guān)SQL Server批量插入數(shù)據(jù)內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信