語言
執行 SQL Server 大量複製作業最簡單的方式為執行對資料庫的單一作業。 根據預設，大量複製作業會做為隔離作業執行：該複製作業會以非交易的方式進行，且沒有機會復原。
注意
若要在發生錯誤後回滾全部或部分大量複製，請指定 SqlBulkCopyOptions.UseInternalTransaction 以使用每批次事務，或在現有事務中執行複製。
SqlBulkCopy 也可搭配 System.Transactions 運作，如果連線是以隱含或明確方式登錄。
如需詳細資訊，請參閱交易和大量複製作業。
執行大量複製作業的一般步驟如下：
連接到來源伺服器，並取得要複製的資料。 如果可以從 IDataReader 或 DataTable 物件中擷取資料，則資料也可來自其他來源。
連接到目的地伺服器（除非您想讓
SqlBulkCopy幫您建立連線）。建立 SqlBulkCopy 物件並設定必要的屬性。
設定屬性
DestinationTableName以表示批量插入操作的目標表。請調用其中一個
WriteToServer方法。可選擇性地更新屬性，必要時再呼叫
WriteToServer一次。呼叫 Close，或將大量複製作業包裝在
Using陳述式內。
警告
使用匹配的來源與目標欄位資料型態。 若類型不匹配，SqlBulkCopy 嘗試依據 Value 的規則，將每個來源值轉換為目標資料型態。 轉換會影響效能並引發意外錯誤。 例如，從 Double 轉換為 Decimal 對某些值可能會失敗。
範例
以下主控台應用程式示範如何用 SqlBulkCopy 類別載入資料。 此範例使用 SqlDataReader，將資料從 SQL Server AdventureWorksProduction.Product 資料庫複製到同一資料庫中的類似資料表。
重要
在執行此範例前，請建立 Bulk Copy 範例設定中描述的工作表。 範例展示了 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();
}
另請參閱
- SQL Server 中的大量複製操作
- ADO.NET 概觀 \(注意: 此部分為機器翻譯\)