When you need to insert a great deal of rows into database (for example, when importing data from a flat file or when importing data from one database into another one), and you need to do it programmatically because it needs to be pre-processed on the fly, then you should use SqlBulkCopy class.
The class uses Tabular Data Stream for fast transmitting data from client to a database server (therefore you'll see a bunch of "strange" INSERT BULK statements in a SQL Profiler):
DataTable dataTable = new DataTable();
...
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "dbo.DestinationTale";
bulkCopy.WriteToServer(dataTable);
}
Also try different values for SqlBulkCopy.BatchSize to adjust performance for your case.
P.S. the tip applies only to MS SQL Server.
This is SQL Server-specific, right? Probably worth mentioning that for this kind of tip -- not every .NET developer uses SQL Server.
Jeffrey McManus 11/14/2007 8:32:58 PM
Jeffrey, you are right. The tip applies only to MS SQL Server. I think I need to add section "Applies to:" to all tips. Sometimes .NET version also should be specified.
kostya.ly 11/14/2007 9:14:18 PM
Nifty. Does this also handle updated and deleted records in the DataTable in addition to newly added records?
Scott 11/15/2007 1:52:31 AM
No, it doesn't. It can be used only to write data.
kostya.ly 11/15/2007 1:58:27 AM
This is really helpful. It worked well & speed is also increased toooooooo much. Thanks. --I have bookmarked your site, but tips are not added on daily basis, right?
Akash 7/25/2008 1:19:04 PM