What is the best and fast way to insert 2 million rows of data into SQL Server?

Technology CommunityCategory: ADO.NETWhat is the best and fast way to insert 2 million rows of data into SQL Server?
VietMX Staff asked 3 years ago

You can try with SqlBulkCopy class. Another option is using bcp (Bulk Copy Program) utility.

Consider:

// connect to SQL
using (SqlConnection connection = 
        new SqlConnection(connString))
{
    // make sure to enable triggers
    SqlBulkCopy bulkCopy = 
        new SqlBulkCopy
        (
        connection, 
        SqlBulkCopyOptions.TableLock | 
        SqlBulkCopyOptions.FireTriggers | 
        SqlBulkCopyOptions.UseInternalTransaction,
        null
        );

    // set the destination table name
    bulkCopy.DestinationTableName = this.tableName;
    connection.Open();

    // write the data in the "dataTable"
    bulkCopy.WriteToServer(dataTable);
    connection.Close();
}
// reset
this.dataTable.Clear();