I use a staging strategy where I BULK COPY the data into SQL Server if I have that sort of volume. This entails:
Have a staging table with fields matching your source data column names
All fields in the staging table should be varchar
Read in the source data
Get an empty record set from the database for the staging table
insert the source data into the empty DataTable
Bulk Copy the DataTable into sql server
private int BulkCopy(DataTable dtTable, string sTableName, SqlConnection oConn)
{
try
{
SqlBulkCopy oBC = new SqlBulkCopy(oConn);
oBC.BulkCopyTimeout = 60000;
oBC.DestinationTableName = sTableName;
oBC.WriteToServer(dtTable);
return dtTable.Rows.Count;
}
catch (Exception ex)
{
throw ex;
}
}
Then use a stored procedure to transform your staging data to the final table.