语言

单次大容量复制操作

执行 SQL Server 大容量复制操作的最简单方法是针对数据库执行单次操作。 默认情况下,大容量复制操作作为独立的操作执行:复制操作以非事务的方式执行,不可对其进行回滚。

备注

若要在发生错误后回滚全部或部分大容量复制,请指定 SqlBulkCopyOptions.UseInternalTransaction 以使用每批事务,或在现有事务中执行复制。 在连接被隐式或显式登记时,SqlBulkCopy也可与System.Transactions一起使用。

有关详细信息,请参阅事务和批量复制操作。

执行大容量复制操作的常规步骤如下:

  1. 连接到源服务器并获取要复制的数据。 如果可以从 IDataReader 或 DataTable 对象检索数据,那么数据也可以来自其他源。

  2. 连接到目标服务器(除非要 SqlBulkCopy 为你建立连接)。

  3. 创建 SqlBulkCopy 对象并设置必要的属性。

  4. 设置 DestinationTableName 属性以指示批量插入操作的目标表。

  5. 调用其中 WriteToServer 一种方法。

  6. (可选)根据需要更新属性并再次调用 WriteToServer 。

  7. 调用 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();
}

请参阅