Bulk-Insert Options for ADO.NET

Clients commonly ask me how to bulk insert data into SQL Server from ADO.NET applications. There's no easy answer to this question because ADO.NET doesn't have any built-in bulk-insert objects. The most straightforward method for performing bulk-insert operations from ADO.NET is loading the DataSet with multiple large groups of rows, then sending updates to SQL Server. You can also use the SqlCommand object inside either a stored procedure or a parameterized INSERT statement to insert the data. However, both of these methods are slow because they perform one insert action for each inserted row. Fortunately, the following alternative methods provide better performance. Remember, for the fastest import speed, drop any affected indexes or import them into temporary tables.

bcp

Using the command-line bulk copy program (bcp) to perform bulk inserts from your ADO.NET application is an obvious solution because bcp provides a fast, efficient conduit for moving data between flat files and SQL Server. Creating the format file for use in command-line runs can be time-consuming, but after you create the file, you can call bcp by using the .NET System.Diagnostic namespace.

SQL-DMO BulkCopy Object

You can also use the SQL Distributed Management Object (SQL-DMO) BulkCopy object to handle bulk inserts. The BulkCopy object's ImportData method is easy to use and provides a high-performance mechanism for importing data into SQL Server. SQL-DMO is a COM-based library, so you have to use the System.Runtime.InteropServices namespace from your ADO.NET application to use the BulkCopy object.

DTS Bulk Insert Task

Like SQL-DMO, Data Transformation Services (DTS) is a COM-based object library, so if you want to use DTS for bulk inserts directly, you need to use the System.Runtime.InteropServices .NET COM-Interop namespace. However, unlike SQL-DMO, DTS lets you create standalone, executable packages that you can save and execute from the command line later, making DTS more flexible than SQL-DMO. You can use the graphical DTS Designer to create and test a package that executes the Bulk Insert task. After you test the package to make sure it works, you can call DTSRun with the package name—using the System.Diagnostic namespace—to execute the package from your ADO.NET application.

T-SQL BULK INSERT Statement

Using the T-SQL BULK INSERT statement in combination with the ADO.NET SqlCommand object is usually the simplest solution to the bulk-insert dilemma. Of the four methods described here, the BULK INSERT statement is the fastest way to load data into SQL Server. The statement runs in process with SQL Server and performs minimal logging. To use the BULK INSERT statement from an ADO.NET application, you need to add the System.Data.SqlClient namespace to your application, then execute the BULK INSERT statement by using an instance of the SqlCommand object.