多个批量复制操作
可以使用 SqlBulkCopy 类的单个实例执行多次大容量复制操作。 如果复制之间的操作参数已更改（例如，目标表的名称），必须首先更新它们，之后再对任何 WriteToServer 方法进行任何后续调用，如以下示例所示。 除非已明确更改，否则将所有属性值保持为与给定实例的较早大容量复制上的属性值相同。
备注
与每个操作使用单独实例相比，使用 SqlBulkCopy 的同一实例执行多次大容量复制操作通常效率更高。
在使用同一 SqlBulkCopy 对象执行多次大容量复制操作时，对于每个操作中的源信息或目标信息相同与否没有限制。 但是，必须确保每次写入服务器时正确设置列关联信息。
重要
除非已按批量复制示例设置中所述创建了工作表，否则此示例不会运行。 提供此代码是为了演示仅使用 SqlBulkCopy 时的语法。 如果源表和目标表位于同一 SQL Server 实例中，可以更便捷地使用 Transact-SQL INSERT … SELECT 语句复制数据。
static void Main()
{
var connectionString = GetConnectionString();
// Open a connection to the AdventureWorks database.
using (SqlConnection connection =
new(connectionString))
{
connection.Open();
// Empty the destination tables.
SqlCommand deleteHeader = new(
"DELETE FROM dbo.BulkCopyDemoOrderHeader;",
connection);
deleteHeader.ExecuteNonQuery();
SqlCommand deleteDetail = new(
"DELETE FROM dbo.BulkCopyDemoOrderDetail;",
connection);
deleteDetail.ExecuteNonQuery();
// Perform an initial count on the destination
// table with matching columns.
SqlCommand countRowHeader = new(
"SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderHeader;",
connection);
long countStartHeader = Convert.ToInt32(
countRowHeader.ExecuteScalar());
Console.WriteLine(
"Starting row count for Header table = {0}",
countStartHeader);
// Perform an initial count on the destination
// table with different column positions.
SqlCommand countRowDetail = new(
"SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderDetail;",
connection);
long countStartDetail = Convert.ToInt32(
countRowDetail.ExecuteScalar());
Console.WriteLine(
"Starting row count for Detail table = {0}",
countStartDetail);
// Get data from the source table as a SqlDataReader.
// The Sales.SalesOrderHeader and Sales.SalesOrderDetail
// 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.
SqlCommand headerData = new(
"SELECT [SalesOrderID], [OrderDate], " +
"[AccountNumber] FROM [Sales].[SalesOrderHeader] " +
"WHERE [AccountNumber] = @accountNumber;",
connection);
SqlParameter parameterAccount = new()
{
ParameterName = "@accountNumber",
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input,
Value = "10-4020-000034"
};
headerData.Parameters.Add(parameterAccount);
SqlDataReader readerHeader = headerData.ExecuteReader();
// Get the Detail data in a separate connection.
using (SqlConnection connection2 = new(connectionString))
{
connection2.Open();
SqlCommand sourceDetailData = new(
"SELECT [Sales].[SalesOrderDetail].[SalesOrderID], [SalesOrderDetailID], " +
"[OrderQty], [ProductID], [UnitPrice] FROM [Sales].[SalesOrderDetail] " +
"INNER JOIN [Sales].[SalesOrderHeader] ON [Sales].[SalesOrderDetail]." +
"[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] " +
"WHERE [AccountNumber] = @accountNumber;", connection2);
SqlParameter accountDetail = new()
{
ParameterName = "@accountNumber",
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input,
Value = "10-4020-000034"
};
sourceDetailData.Parameters.Add(accountDetail);
SqlDataReader readerDetail = sourceDetailData.ExecuteReader();
// Create the SqlBulkCopy object.
using (SqlBulkCopy bulkCopy =
new(connectionString))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoOrderHeader";
// Guarantee that columns are mapped correctly by
// defining the column mappings for the order.
bulkCopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID");
bulkCopy.ColumnMappings.Add("OrderDate", "OrderDate");
bulkCopy.ColumnMappings.Add("AccountNumber", "AccountNumber");
// Write readerHeader to the destination.
try
{
bulkCopy.WriteToServer(readerHeader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
readerHeader.Close();
}
// Set up the order details destination.
bulkCopy.DestinationTableName = "dbo.BulkCopyDemoOrderDetail";
// Clear the ColumnMappingCollection.
bulkCopy.ColumnMappings.Clear();
// Add order detail column mappings.
bulkCopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID");
bulkCopy.ColumnMappings.Add("SalesOrderDetailID", "SalesOrderDetailID");
bulkCopy.ColumnMappings.Add("OrderQty", "OrderQty");
bulkCopy.ColumnMappings.Add("ProductID", "ProductID");
bulkCopy.ColumnMappings.Add("UnitPrice", "UnitPrice");
// Write readerDetail to the destination.
try
{
bulkCopy.WriteToServer(readerDetail);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
readerDetail.Close();
}
}
// Perform a final count on the destination
// tables to see how many rows were added.
long countEndHeader = Convert.ToInt32(
countRowHeader.ExecuteScalar());
Console.WriteLine("{0} rows were added to the Header table.",
countEndHeader - countStartHeader);
long countEndDetail = Convert.ToInt32(
countRowDetail.ExecuteScalar());
Console.WriteLine("{0} rows were added to the Detail table.",
countEndDetail - countStartDetail);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
}
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( _
"DELETE FROM dbo.BulkCopyDemoOrderHeader;", connection)
deleteHeader.ExecuteNonQuery()
deleteHeader.Dispose()
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( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderHeader;", _
connection)
Dim countStartHeader As Long = System.Convert.ToInt32( _
countRowHeader.ExecuteScalar())
Console.WriteLine("Starting row count for Header table = {0}", _
countStartHeader)
' 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.
' The Sales.SalesOrderHeader and Sales.SalesOrderDetail
' 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"
headerData.Parameters.Add(parameterAccount)
Dim readerHeader As SqlDataReader = _
headerData.ExecuteReader()
' 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 " & _
"[Sales].[SalesOrderHeader] " & _
"ON [Sales].[SalesOrderDetail].[SalesOrderID] = " & _
"[Sales].[SalesOrderHeader].[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"
sourceDetailData.Parameters.Add( _
accountDetail)
Dim readerDetail As SqlDataReader = _
sourceDetailData.ExecuteReader()
' Create the SqlBulkCopy object.
Using bulkCopy As SqlBulkCopy = _
New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = "dbo.BulkCopyDemoOrderHeader"
' Guarantee that columns are mapped correctly by
' defining the column mappings for the order.
bulkCopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID")
bulkCopy.ColumnMappings.Add("OrderDate", "OrderDate")
bulkCopy.ColumnMappings.Add("AccountNumber", "AccountNumber")
' Write readerHeader to the destination.
Try
bulkCopy.WriteToServer(readerHeader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
readerHeader.Close()
End Try
' Set up the order details destination.
bulkCopy.DestinationTableName = "dbo.BulkCopyDemoOrderDetail"
' Clear the ColumnMappingCollection.
bulkCopy.ColumnMappings.Clear()
' Add order detail column mappings.
bulkCopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID")
bulkCopy.ColumnMappings.Add("SalesOrderDetailID", "SalesOrderDetailID")
bulkCopy.ColumnMappings.Add("OrderQty", "OrderQty")
bulkCopy.ColumnMappings.Add("ProductID", "ProductID")
bulkCopy.ColumnMappings.Add("UnitPrice", "UnitPrice")
' Write readerDetail to the destination.
Try
bulkCopy.WriteToServer(readerDetail)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
readerDetail.Close()
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( _
countRowHeader.ExecuteScalar())
Console.WriteLine("{0} rows were added to the Header table.", _
countEndHeader - countStartHeader)
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.")
Console.ReadLine()
End Using
End Using
End Sub
Private Function GetConnectionString() As String
Throw New NotImplementedException()
End Function
End Module