语言
执行 SQL Server 大容量复制操作的最简单方法是针对数据库执行单次操作。 默认情况下,大容量复制操作作为独立的操作执行:复制操作以非事务的方式执行,不可对其进行回滚。
备注
若要在发生错误后回滚全部或部分大容量复制,请指定 SqlBulkCopyOptions.UseInternalTransaction 以使用每批事务,或在现有事务中执行复制。 在连接被隐式或显式登记时,SqlBulkCopy也可与System.Transactions一起使用。
有关详细信息,请参阅事务和批量复制操作。
执行大容量复制操作的常规步骤如下:
连接到源服务器并获取要复制的数据。 如果可以从 IDataReader 或 DataTable 对象检索数据,那么数据也可以来自其他源。
连接到目标服务器(除非要
SqlBulkCopy为你建立连接)。创建 SqlBulkCopy 对象并设置必要的属性。
设置
DestinationTableName属性以指示批量插入操作的目标表。调用其中
WriteToServer一种方法。(可选)根据需要更新属性并再次调用
WriteToServer。调用 Close,或在
Using语句中包装大容量复制操作。
注意
使用相匹配的源列和目标列数据类型。 如果类型不匹配,SqlBulkCopy 则尝试按照 Value 的规则将每个源值转换为目标数据类型。 转换可能会影响性能并导致意外错误。 例如,对于某些值,从 Double 转换为 Decimal 可能会失败。
示例
以下控制台应用程序演示如何使用 SqlBulkCopy 类加载数据。 该示例使用SqlDataReader将数据从Production.ProductSQL ServerAdventureWorks数据库中复制到同一数据库中的类似表。
重要
运行此示例之前,请创建 批量复制示例设置中所述的表。 此示例演示了 SqlBulkCopy 语法。 如果源表和目标表位于同一SQL Server实例中,请使用 Transact-SQL (T-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 = {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 = {countEnd}");
Console.WriteLine($"{countEnd - countStart} rows were added.");
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
}
Imports Microsoft.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()
Dim command As 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();
}