Sql大量刪除/插入

在這裡主要介紹下Sql批量插入的用法，讓我們先看下大量刪除一般如何來做。
1.大量刪除很簡單，大家可能都用過：

   

 DELETE FROM TestTable WHERE ID IN (1, 3, 54, 68)  --sql2005下運行通過

 

    當使用者在介面上不連續的選擇多項進行刪除時，該語句比迴圈調用多次刪除或多條delete語句中間加分號一次調用等方法都高效的多。

2.批量插入的寫法：

 

代碼

    sql寫法：

    INSERT INTO TestTable SELECT 1, 'abc' UNION SELECT  2, 'bcd'  UNION SELECT 3, 'cde'   
--TestTable表沒有主鍵，ID不是主鍵

    oracle寫法：

    INSERT INTO TestTable SELECT 1, 'abc' From daul UNION SELECT  2, 'bcd' From daul    
--TestTable表沒有主鍵，ID不是主鍵

 

　　曾經測試過，這種寫法插入1000條資料比迴圈調用1000次insert或1000條insert語句簡單疊加一次調用要高效得多，大概快20多倍（調試狀態不是太准）。其實很簡單，就用了個union（union all 也可以），但當時得出測試結果時還是很驚喜的。
　　另外，union和union all是有區別的，UNION在進行錶鏈接後會篩選掉重複的記錄，所以在錶鏈接後會對所產生的結果集進行排序運算，重複資料刪除的記錄再返回結果。 而UNION ALL只是簡單的將兩個結果合并後就返回。這樣，如果返回的兩個結果集中有重複的資料，那麼返回的結果集就會包含重複的資料了。從效率上說，UNION ALL 要比UNION快很多，所以，如果可以確認合并的兩個結果集中不包含重複的資料的話，那麼就使用UNION ALL。

　　但是要使用上述方法來進行批量插入的話，需要兩個條件：

    　　1、表不能有主鍵或者主鍵是資料庫預設的（sql用自動遞增列，oracle用序列）

    　　2、組合sql語句時只能直接用字串串連，不能用參數化sql語句的寫法（就是在組合的sql中用@parm做預留位置，再給Command對象添加Parameter）

    以上兩條任意一條不滿足，效率的提高都不明顯。

    由於是考慮到大資料量的批量插入，於是我想到了ADO.NET2.0的一個新的特性：SqlBulkCopy。有關這個的效能，很早之前我是親自做過效能測試的，效率非常高。

 

代碼

private static long SqlBulkCopyInsert() 
{ 
//Stopwatch stopwatch = new Stopwatch(); 
//stopwatch.Start(); 
DataTable dataTable = CreateDataTable(); 
string passportKey; 
for (int i = 0; i < count; i++) 
{ 
passportKey = Guid.NewGuid().ToString(); 
DataRow dataRow = dataTable.NewRow(); 
dataRow[0] = passportKey; 
dataTable.Rows.Add(dataRow); 
} 

SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString); 
sqlBulkCopy.DestinationTableName = "Passport"; 
sqlBulkCopy.BatchSize = dataTable.Rows.Count; 
//SqlConnection sqlConnection = new SqlConnection(connectionString); 
//sqlConnection.Open(); 
if (dataTable!=null && dataTable.Rows.Count!=0) 
{ 
sqlBulkCopy.WriteToServer(dataTable); 
} 
sqlBulkCopy.Close(); 
//sqlConnection.Close(); 
//stopwatch.Stop(); 
//return stopwatch.ElapsedMilliseconds; 
}