Batch delete/Insert SQL statements

Here we will mainly introduce the usage of SQL batch insertion. Let's take a look at how batch deletion is generally done.
1. Batch deletion is simple, You may have used:

 Delete from TestTable where id in (1, 3, 54, 68) -- run through

 

When you choose to delete multiple objects in a non-consecutive manner on the interface, this statement is more efficient than multiple methods such as multiple deletions in a loop call or one call of the intermediate bonus points in multiple delete statements.

2. Batch insert statement:

 

Code

 SQL statement:

Insert into TestTable SELECT 1, 'abc' union select 2, 'bcd' union select 3, 'cde'
-- The TestTable table does not have a primary key, and the ID is not the primary key.

Oracle statement:

Insert into TestTable SELECT 1, 'abc' From daul union select 2, 'bcd' From daul
-- The TestTable table does not have a primary key, and the ID is not the primary key.

 

Once tested, inserting 1000 pieces of data in this way is much more efficient than simply adding one call to 1000 insert or 1000 insert statements in a loop call, which is about 20 times faster (the debugging status is not accurate ). In fact, it was very easy to use a union (union all can also be used), but it was still very pleasant to get the test results.
In addition, there is a difference between union and union all. UNION filters out duplicate records after table links are performed. Therefore, after table links are performed, union performs sorting on the generated result sets, delete duplicate records and return results. Union all simply merges the two results and returns them. In this way, if duplicate data exists in the two returned result sets, the returned result sets will contain duplicate data. In terms of efficiency, union all is much faster than UNION. Therefore, if we can confirm that the two results of the merge do not contain duplicate data, we will use union all.

However, to use the above method for batch insertion, two conditions are required:

1. the table cannot have a primary key or the primary key is the default database (SQL uses auto-incrementing columns and oracle uses sequences)

2. When you combine SQL statements, you can only connect them with strings. You cannot use parameterized SQL statements (that is, @ parm is used as a placeholder in the combined SQL statement and Parameter is added to the Command object)

If either of the above two statements is not met, the efficiency improvement is not obvious.

Considering the large data volume of batch inserts, I came up with a new feature of ADO. NET2.0: SqlBulkCopy. For this performance, I personally tested the performance a long time ago and the efficiency was very high.

 

Code

 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; 
}