Export (0) Print
Expand All

Performing a Single Bulk Copy Operation 

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.

NoteNote

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 Performing a Bulk Copy Operation in a Transaction.

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.

Caution noteCaution

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.

Example

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

NoteImportant

This sample will not run unless you have created the work tables as described in Creating Tables for the Bulk Copy Examples. 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.

using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        // Open a sourceConnection to the AdventureWorks database.
        using (SqlConnection sourceConnection =
                   new SqlConnection(connectionString))
        {
            sourceConnection.Open();

            // Perform an initial count on the destination table.
            SqlCommand commandRowCount = new SqlCommand(
                "SELECT COUNT(*) FROM " +
                "dbo.BulkCopyDemoMatchingColumns;",
                sourceConnection);
            long countStart = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Starting row count = {0}", countStart);

            // Get data from the source table as a SqlDataReader.
            SqlCommand commandSourceData = new SqlCommand(
                "SELECT ProductID, Name, " +
                "ProductNumber " +
                "FROM Production.Product;", sourceConnection);
            SqlDataReader reader =
                commandSourceData.ExecuteReader();

            // Open the destination connection. In the real world you would 
            // not use SqlBulkCopy to move data from one table to the other 
            // in the same database. This is for demonstration purposes only.
            using (SqlConnection destinationConnection =
                       new SqlConnection(connectionString))
            {
                destinationConnection.Open();

                // Set up the bulk copy object. 
                // Note that the column positions in the source
                // data reader match the column positions in 
                // the destination table so there is no need to
                // map columns.
                using (SqlBulkCopy bulkCopy =
                           new SqlBulkCopy(destinationConnection))
                {
                    bulkCopy.DestinationTableName =
                        "dbo.BulkCopyDemoMatchingColumns";

                    try
                    {
                        // Write from the source to the destination.
                        bulkCopy.WriteToServer(reader);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        // Close the SqlDataReader. The SqlBulkCopy
                        // object is automatically closed at the end
                        // of the using block.
                        reader.Close();
                    }
                }

                // Perform a final count on the destination 
                // table to see how many rows were added.
                long countEnd = System.Convert.ToInt32(
                    commandRowCount.ExecuteScalar());
                Console.WriteLine("Ending row count = {0}", countEnd);
                Console.WriteLine("{0} rows were added.", countEnd - countStart);
                Console.WriteLine("Press Enter to finish.");
                Console.ReadLine();
            }
        }
    }

    private static string GetConnectionString()
        // To avoid storing the sourceConnection string in your code, 
        // you can retrieve it from a configuration file. 
    {
        return "Data Source=(local); " +
            " Integrated Security=true;" +
            "Initial Catalog=AdventureWorks;";
    }
}

Performing a Bulk Copy Operation Using Transact-SQL and the Command Class

When using .NET Framework version 1.1 or earlier (which does not support the SqlBulkCopy class), you can also execute the Transact-SQL BULK INSERT statement by using the SqlCommand object. Note that using this technique is completely unrelated to using the bulk copy functionality provided by the .NET Framework Data Provider for SQL Server.

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

NoteNote

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();
}

See Also

Community Additions

ADD
Show:
© 2014 Microsoft