Inserting records using SqlBulkCopy

When there comes the necessity of inserting a lots of rows into database (for example, when importing data from a flat file (excel, csv) 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)

SqlBulkCopy

Microsoft SQL Server includes a popular command-prompt utility named bcp for moving data from one table to another, whether on a single server or between servers. The SqlBulkCopy class lets you write managed code solutions that provide similar functionality. There are other ways to load data into a SQL Server table (INSERT statements, for example), but SqlBulkCopy offers a significant performance advantage over them.

Limitation of SqlBulkCopy

The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance. The limitation is the destination should be Microsoft Sql server.

Code Example

 DataTable dataTable = new DataTable();

//load data to datatable

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))

{

bulkCopy.DestinationTableName = "dbo.DestinationTale";

bulkCopy.WriteToServer(dataTable);

}