# Multiple Bulk Copy Operations

You can perform multiple bulk copy operations using a single instance of a SqlBulkCopy class. If the operation parameters change between copies (for example, the name of the destination table), you must update them prior to any subsequent calls to any of the WriteToServer methods, as demonstrated in the following example. Unless explicitly changed, all property values remain the same as they were on the previous bulk copy operation for a given instance.

Note

Performing multiple bulk copy operations using the same instance of SqlBulkCopy is usually more efficient than using a separate instance for each operation.

If you perform several bulk copy operations using the same SqlBulkCopy object, there are no restrictions on whether source or target information is equal or different in each operation. However, you must ensure that column association information is properly set each time you write to the server.

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.

using System.Data.SqlClient;

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

// Empty the destination tables.
connection);
SqlCommand deleteDetail = new SqlCommand(
"DELETE FROM dbo.BulkCopyDemoOrderDetail;",
connection);
deleteDetail.ExecuteNonQuery();

// Perform an initial count on the destination
//  table with matching columns.
connection);
Console.WriteLine(
"Starting row count for Header table = {0}",

// Perform an initial count on the destination
// table with different column positions.
SqlCommand countRowDetail = new SqlCommand(
"SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderDetail;",
connection);
long countStartDetail = System.Convert.ToInt32(
countRowDetail.ExecuteScalar());
Console.WriteLine(
"Starting row count for Detail table = {0}",
countStartDetail);

// Get data from the source table as a SqlDataReader.
// tables are quite large and could easily cause a timeout
// if all data from the tables is added to the destination.
// To keep the example simple and quick, a parameter is
// used to select only orders for a particular account
// as the source for the bulk insert.
"SELECT [SalesOrderID], [OrderDate], " +
"WHERE [AccountNumber] = @accountNumber;",
connection);
SqlParameter parameterAccount = new SqlParameter();
parameterAccount.ParameterName = "@accountNumber";
parameterAccount.SqlDbType = SqlDbType.NVarChar;
parameterAccount.Direction = ParameterDirection.Input;
parameterAccount.Value = "10-4020-000034";

// Get the Detail data in a separate connection.
using (SqlConnection connection2 = new SqlConnection(connectionString))
{
connection2.Open();
SqlCommand sourceDetailData = new SqlCommand(
"SELECT [Sales].[SalesOrderDetail].[SalesOrderID], [SalesOrderDetailID], " +
"[OrderQty], [ProductID], [UnitPrice] FROM [Sales].[SalesOrderDetail] " +
"INNER JOIN [Sales].[SalesOrderHeader] ON [Sales].[SalesOrderDetail]." +
"WHERE [AccountNumber] = @accountNumber;", connection2);

SqlParameter accountDetail = new SqlParameter();
accountDetail.ParameterName = "@accountNumber";
accountDetail.SqlDbType = SqlDbType.NVarChar;
accountDetail.Direction = ParameterDirection.Input;
accountDetail.Value = "10-4020-000034";

// Create the SqlBulkCopy object.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName =

// Guarantee that columns are mapped correctly by
// defining the column mappings for the order.

try
{
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
}

// Set up the order details destination.
bulkCopy.DestinationTableName ="dbo.BulkCopyDemoOrderDetail";

// Clear the ColumnMappingCollection.
bulkCopy.ColumnMappings.Clear();

// Add order detail column mappings.

// Write readerDetail to the destination.
try
{
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
}
}

// Perform a final count on the destination
// tables to see how many rows were added.
long countEndDetail = System.Convert.ToInt32(
countRowDetail.ExecuteScalar());
Console.WriteLine("{0} rows were added to the Detail table.",
countEndDetail - countStartDetail);
Console.WriteLine("Press Enter to finish.");
}
}
}

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

Imports System.Data.SqlClient

Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()

' Open a connection to the AdventureWorks database.
Using connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()

' Empty the destination tables.
Dim deleteHeader As New SqlCommand( _
Dim deleteDetail As New SqlCommand( _
"DELETE FROM dbo.BulkCopyDemoOrderDetail;", connection)
deleteDetail.ExecuteNonQuery()

' Perform an initial count on the destination table
' with matching columns.
Dim countRowHeader As New SqlCommand( _
connection)
Dim countStartHeader As Long = System.Convert.ToInt32( _
Console.WriteLine("Starting row count for Header table = {0}", _

' Perform an initial count on the destination table
' with different column positions.
Dim countRowDetail As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderDetail;", _
connection)
Dim countStartDetail As Long = System.Convert.ToInt32( _
countRowDetail.ExecuteScalar())
Console.WriteLine("Starting row count for Detail table = " & _
countStartDetail)

' Get data from the source table as a SqlDataReader.
' tables are quite large and could easily cause a timeout
' if all data from the tables is added to the destination.
' To keep the example simple and quick, a parameter is
' used to select only orders for a particular account as
' the source for the bulk insert.
Dim headerData As SqlCommand = New SqlCommand( _
"SELECT [SalesOrderID], [OrderDate], " & _
"[AccountNumber] FROM [Sales].[SalesOrderHeader] " & _
"WHERE [AccountNumber] = @accountNumber;", _
connection)

Dim parameterAccount As SqlParameter = New SqlParameter()
parameterAccount.ParameterName = "@accountNumber"
parameterAccount.SqlDbType = SqlDbType.NVarChar
parameterAccount.Direction = ParameterDirection.Input
parameterAccount.Value = "10-4020-000034"

' Get the Detail data in a separate connection.
Using connection2 As SqlConnection = New SqlConnection(connectionString)
connection2.Open()

Dim sourceDetailData As SqlCommand = New SqlCommand( _
"SELECT [Sales].[SalesOrderDetail].[SalesOrderID], " & _
"[SalesOrderDetailID], [OrderQty], [ProductID], [UnitPrice] " & _
"FROM [Sales].[SalesOrderDetail] INNER JOIN " & _
"ON [Sales].[SalesOrderDetail].[SalesOrderID] = " & _
"WHERE [AccountNumber] = @accountNumber;", connection2)

Dim accountDetail As SqlParameter = New SqlParameter()
accountDetail.ParameterName = "@accountNumber"
accountDetail.SqlDbType = SqlDbType.NVarChar
accountDetail.Direction = ParameterDirection.Input
accountDetail.Value = "10-4020-000034"
accountDetail)

' Create the SqlBulkCopy object.
Using bulkCopy As SqlBulkCopy = _
New SqlBulkCopy(connectionString)

' Guarantee that columns are mapped correctly by
' defining the column mappings for the order.

Try
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
End Try

' Set up the order details destination.
bulkCopy.DestinationTableName = "dbo.BulkCopyDemoOrderDetail"

' Clear the ColumnMappingCollection.
bulkCopy.ColumnMappings.Clear()

' Add order detail column mappings.

' Write readerDetail to the destination.
Try
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
End Try
End Using

' Perform a final count on the destination tables
' to see how many rows were added.
Dim countEndHeader As Long = System.Convert.ToInt32( _
Dim countEndDetail As Long = System.Convert.ToInt32( _
countRowDetail.ExecuteScalar())
Console.WriteLine("{0} rows were added to the Detail table.", _
countEndDetail - countStartDetail)

Console.WriteLine("Press Enter to finish.")
End Using
End Using
End Sub

Private Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=(local);" & _
"Integrated Security=true;" & _