Using Sqlbulk
#region Way Two
static void Inserttwo ()
{
Console.WriteLine ("Implementation using bulk insertion");
Stopwatch SW = new Stopwatch ();
DataTable dt = GetTableSchema ();
using (SqlConnection conn = new SqlConnection (strconnmsg))
{
SqlBulkCopy bulkcopy = new SqlBulkCopy (conn);
Bulkcopy.destinationtablename = "Product";
bulkcopy.batchsize = dt. Rows.Count;
Conn. Open ();
Sw. Start ();
for (int i = 0; i < totalrow;i++)
{
DataRow dr = dt. NewRow ();
Dr[0] = Guid.NewGuid ();
DR[1] = string. Format ("Commodity", I);
DR[2] = (decimal) i;
Dt. Rows.Add (DR);
}
if (dt! = null && dt. Rows.Count! = 0)
{
Bulkcopy.writetoserver (DT);
Sw. Stop ();
}
Console.WriteLine (String. Format ("Insert {0}" record has a total cost of {1} milliseconds, {2} minutes ", TotalRow, SW.) Elapsedmilliseconds, Getminute (SW. (elapsedmilliseconds)));
}
}
Static DataTable GetTableSchema ()
{
DataTable dt = new DataTable ();
Dt. Columns.addrange (new datacolumn[] {
New DataColumn ("Id", typeof (Guid)),
New DataColumn ("Name", typeof (String)),
New DataColumn ("Price", typeof (Decimal))});
return DT;
}
#endregion
Inserting 100w Records is more than 8s, is not very slip.
Inserting data using TVPs (table-valued parameters)
Support for TVPS starting from SQL Server 2008. Create the cache table Producttemp and execute the following SQL.
CREATE TYPE producttemp as TABLE (Id uniqueidentifier PRIMARY key,name VARCHAR () not Null,price DECIMAL (18,2) not NUL L
It takes more than 11 seconds to insert a 100w record.
SQL BULK INSERT data into SQL Server in a highly efficient way