Single Bulk Copy Operations

 

The simplest approach to performing a SQL Server bulk copy operation is to perform a single operation against a database. By default, a bulk copy operation is performed as an isolated operation: the copy operation occurs in a non-transacted way, with no opportunity for rolling it back.

System_CAPS_ICON_note.jpg Note

If you need to roll back all or part of the bulk copy when an error occurs, you can either use a SqlBulkCopy-managed transaction, or perform the bulk copy operation within an existing transaction. SqlBulkCopy will also work with System.Transactions if the connection is enlisted (implicitly or explicitly) into a System.Transactions transaction.

For more information, see Transaction and Bulk Copy Operations.

The general steps for performing a bulk copy operation are as follows:

  1. Connect to the source server and obtain the data to be copied. Data can also come from other sources, if it can be retrieved from an IDataReader or DataTable object.

  2. Connect to the destination server (unless you want SqlBulkCopy to establish a connection for you).

  3. Create a SqlBulkCopy object, setting any necessary properties.

  4. Set the DestinationTableName property to indicate the target table for the bulk insert operation.

  5. Call one of the WriteToServer methods.

  6. Optionally, update properties and call WriteToServer again as necessary.

  7. Call Close, or wrap the bulk copy operations within a Using statement.

System_CAPS_ICON_caution.jpg Caution

We recommend that the source and target column data types match. If the data types do not match, SqlBulkCopy attempts to convert each source value to the target data type, using the rules employed by Value. Conversions can affect performance, and also can result in unexpected errors. For example, a Double data type can be converted to a Decimal data type most of the time, but not always.

The following console application demonstrates how to load data using the SqlBulkCopy class. In this example, a SqlDataReader is used to copy data from the Production.Product table in the SQL ServerAdventureWorks database to a similar table in the same database.

System_CAPS_ICON_important.jpg Important

This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. This code is provided to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are located in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT … SELECT statement to copy the data.

[!CODE [DataWorks BulkCopy.Single#1](../CodeSnippet/VS_Snippets_ADO.NET/DataWorks BulkCopy.Single#1)]

The following example illustrates how to use the ExecuteNonQuery method to execute the BULK INSERT statement.

System_CAPS_ICON_note.jpg Note

The file path for the data source is relative to the server. The server process must have access to that path in order for the bulk copy operation to succeed.

using (SqlConnection connection = New SqlConnection(connectionString))  
{  
string queryString =  "BULK INSERT Northwind.dbo.[Order Details] " +  
    "FROM 'f:\mydata\data.tbl' " +  
    "WITH ( FORMATFILE='f:\mydata\data.fmt' )";  
connection.Open();  
SqlCommand command = new SqlCommand(queryString, connection);  
  
command.ExecuteNonQuery();  
}  

Bulk Copy Operations in SQL Server
ADO.NET Managed Providers and DataSet Developer Center

Show: