单次大容量复制操作
执行 SQL Server 大容量复制操作的最简单方法是针对数据库执行单次操作。 默认情况下，大容量复制操作作为独立的操作执行：复制操作以非事务的方式执行，不可对其进行回滚。
备注
如果在发生错误时需要回滚全部或部分大容量复制，可以使用 SqlBulkCopy 托管的事务，或者在现有事务内执行大容量复制操作。 如果连接在 System.Transactions 事务中（显式或隐式）登记，SqlBulkCopy 也将适用于 System.Transactions。
有关详细信息，请参阅事务和批量复制操作。
执行大容量复制操作的常规步骤如下：
连接到源服务器并获取要复制的数据。 如果可以从 IDataReader 或 DataTable 对象检索数据，那么数据也可以来自其他源。
连接到目标服务器（除非希望 SqlBulkCopy 为你建立连接）。
创建 SqlBulkCopy 对象，设置任何必要的属性。
设置 DestinationTableName 属性以指示执行批量插入操作的目标表。
调用 WriteToServer 方法之一。
可以选择更新属性并根据需要再次调用 WriteToServer。
调用 Close，或在
Using语句中包装大容量复制操作。
注意
我们建议使源列与目标列数据类型相匹配。 如果数据类型不匹配，则 SqlBulkCopy 会尝试使用由 Value 部署的规则将每个源值转换为目标数据类型。 转换可能会影响性能，也可能会导致意外错误。 例如，在多数情况下，Double 数据类型可转换为 Decimal 数据类型，但并非总是如此。
示例
下面的控制台应用程序演示了如何使用 SqlBulkCopy 类加载数据。 在此示例中，SqlDataReader 用于将数据从 SQL Server AdventureWorks 数据库的 Production.Product 表复制到相同数据库的一个类似的表中。
重要
除非已按批量复制示例设置中所述创建了工作表，否则此示例不会运行。 提供此代码是为了演示仅使用 SqlBulkCopy 时的语法。 如果源表和目标表位于同一 SQL Server 实例中，可以更便捷地使用 Transact-SQL INSERT … SELECT 语句复制数据。
static void Main()
{
var connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new(connectionString))
{
sourceConnection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoMatchingColumns;",
sourceConnection);
long countStart = Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new(
"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(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(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 = 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();
}
}
}
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a connection to the AdventureWorks database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As New SqlCommand( _
"SELECT ProductID, Name, ProductNumber " & _
"FROM Production.Product;", sourceConnection)
Dim reader As SqlDataReader = 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 destinationConnection As SqlConnection = _
New SqlConnection(connectionString)
destinationConnection.Open()
' Set up the bulk copy object.
' 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 bulkCopy As SqlBulkCopy = _
New SqlBulkCopy(destinationConnection)
bulkCopy.DestinationTableName = _
"dbo.BulkCopyDemoMatchingColumns"
Try
' Write from the source to the destination.
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
' Close the SqlDataReader. The SqlBulkCopy
' object is automatically closed at the end
' of the Using block.
reader.Close()
End Try
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
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()
End Using
End Using
End Sub
Private Function GetConnectionString() As String
Throw New NotImplementedException()
End Function
End Module
使用 Transact-SQL 和命令类执行批量复制操作
下面的示例说明了如何使用 ExecuteNonQuery 方法执行 BULK INSERT 语句。
备注
数据源的文件路径相对于服务器。 服务器进程必须有权访问该路径，才能成功执行大容量复制操作。
Using connection As SqlConnection = New SqlConnection(connectionString)
Dim queryString As String = _
"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()
End Using
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();
}