Single Bulk Copy Operations

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.


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.


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 Server AdventureWorks database to a similar table in the same database.


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.

class methodbase
   public static int Main(string[] args)


      //Get the MethodBase of a method.

      //Get the type
      Type MyType = Type.GetType("System.MulticastDelegate");

      //Get and display the method
      MethodBase Mymethodbase =

      Console.Write("\nMymethodbase = " + Mymethodbase);

      bool Myispublic = Mymethodbase.IsPublic;
      if (Myispublic)
         Console.Write ("\nMymethodbase is a public method");
         Console.Write ("\nMymethodbase is not a public method");

      return 0;
Produces the following output

Mymethodbase = System.Delegate RemoveImpl (System.Delegate)
Mymethodbase is not a public method

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


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' )";
SqlCommand command = new SqlCommand(queryString, connection);

© 2015 Microsoft