Singole operazioni di copia di massaSingle Bulk Copy Operations

L'approccio più semplice per eseguire un'operazione di copia di massa in SQL Server consiste nell'eseguire una singola operazione in un database.The simplest approach to performing a SQL Server bulk copy operation is to perform a single operation against a database. Per impostazione predefinita, un'operazione di copia di massa viene eseguita come operazione isolata: l'operazione di copia avviene in modalità non transazionale, senza la possibilità di eseguirne il rollback.By default, a bulk copy operation is performed as an isolated operation: the copy operation occurs in a non-transacted way, with no opportunity for rolling it back.

Nota

Per eseguire il rollback di una parte o dell'intera copia di massa quando si verifica un errore, è possibile usare una transazione gestita di SqlBulkCopy oppure eseguire la copia di massa in una transazione esistente.If you need to roll back all or part of the bulk copy when an error occurs, you can either use a SqlBulkCopy-managed transaction, or perform the bulk copy operation within an existing transaction. SqlBulkCopy funzionerà anche con System.Transactions se la connessione viene inserita, in modo implicito o esplicito, in un System. Transactions delle transazioni.SqlBulkCopy will also work with System.Transactions if the connection is enlisted (implicitly or explicitly) into a System.Transactions transaction.

Per ulteriori informazioni, vedere delle transazioni e operazioni di copia Bulk.For more information, see Transaction and Bulk Copy Operations.

In generale, i passaggi per eseguire un'operazione di copia di massa sono i seguenti:The general steps for performing a bulk copy operation are as follows:

  1. Connettersi al server di origine per recuperare i dati da copiare.Connect to the source server and obtain the data to be copied. I dati possono provenire anche da altre origini, se possono essere recuperati da un oggettoIDataReader o DataTable.Data can also come from other sources, if it can be retrieved from an IDataReader or DataTable object.

  2. Connettersi al server di destinazione (a meno che non si desidera SqlBulkCopy per stabilire una connessione per l'utente).Connect to the destination server (unless you want SqlBulkCopy to establish a connection for you).

  3. Creare un oggetto SqlBulkCopy, impostando tutte le proprietà necessarie.Create a SqlBulkCopy object, setting any necessary properties.

  4. Impostare il DestinationTableName proprietà per indicare la tabella di destinazione per la maggior parte delle operazioni di inserimento.Set the DestinationTableName property to indicate the target table for the bulk insert operation.

  5. Chiamare uno del WriteToServer metodi.Call one of the WriteToServer methods.

  6. Facoltativamente, aggiornare le proprietà e chiamate WriteToServer nuovamente se necessario.Optionally, update properties and call WriteToServer again as necessary.

  7. Chiamare Close o eseguire il wrapping delle operazioni di copia di massa all'interno di un'istruzione Using.Call Close, or wrap the bulk copy operations within a Using statement.

Attenzione

È consigliabile assicurare che la corrispondenza tra i tipi di dati della colonna di origine e quelli di destinazione.We recommend that the source and target column data types match. Se i tipi di dati non corrispondono, SqlBulkCopy tenta di convertire ogni valore di origine al tipo di dati di destinazione, tramite le regole utilizzate da Value.If the data types do not match, SqlBulkCopy attempts to convert each source value to the target data type, using the rules employed by Value. Le conversioni possono influenzare le prestazioni e possono provocare errori imprevisti.Conversions can affect performance, and also can result in unexpected errors. Ad esempio, non è sempre possibile convertire un tipo di dati Double in Decimal.For example, a Double data type can be converted to a Decimal data type most of the time, but not always.

EsempioExample

Nell'applicazione console riportata di seguito viene illustrato come caricare i dati usando la classe SqlBulkCopy.The following console application demonstrates how to load data using the SqlBulkCopy class. In questo esempio, un SqlDataReader consente di copiare i dati di Production. Product tabella in SQL ServerAdventureWorks database in una tabella simile dello stesso database.In this example, a SqlDataReader is used to copy data from the Production.Product table in the SQL ServerAdventureWorks database to a similar table in the same database.

Importante

Questo esempio non verrà eseguito a meno che non state create le tabelle di lavoro come descritto in l'installazione di esempio copia Bulk.This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. Questo codice viene fornito per illustrare la sintassi per l'utilizzo di SqlBulkCopy solo.This code is provided to demonstrate the syntax for using SqlBulkCopy only. Se la tabella di origine e quella di destinazione risiedono nella stessa istanza di SQL Server, per copiare i dati è più semplice e rapido usare un'istruzione INSERT … SELECT Transact-SQL.If the source and destination tables are located in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT … SELECT statement to copy the data.

using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        // Open a sourceConnection to the AdventureWorks database.
        using (SqlConnection sourceConnection =
                   new SqlConnection(connectionString))
        {
            sourceConnection.Open();

            // Perform an initial count on the destination table.
            SqlCommand commandRowCount = new SqlCommand(
                "SELECT COUNT(*) FROM " +
                "dbo.BulkCopyDemoMatchingColumns;",
                sourceConnection);
            long countStart = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Starting row count = {0}", countStart);

            // Get data from the source table as a SqlDataReader.
            SqlCommand commandSourceData = new SqlCommand(
                "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 SqlConnection(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 SqlBulkCopy(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 = 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();
            }
        }
    }

    private static string GetConnectionString()
        // To avoid storing the sourceConnection string in your code, 
        // you can retrieve it from a configuration file. 
    {
        return "Data Source=(local); " +
            " Integrated Security=true;" +
            "Initial Catalog=AdventureWorks;";
    }
}
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 SqlCommand = 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
        ' To avoid storing the sourceConnection string in your code, 
        ' you can retrieve it from a configuration file. 
        Return "Data Source=(local);" & _
            "Integrated Security=true;" & _
            "Initial Catalog=AdventureWorks;"
    End Function
End Module

Esecuzione di un'operazione di copia di massa con Transact-SQL e la classe CommandPerforming a Bulk Copy Operation Using Transact-SQL and the Command Class

Nell'esempio seguente viene illustrato come usare il metodo ExecuteNonQuery per eseguire l'istruzione BULK INSERT.The following example illustrates how to use the ExecuteNonQuery method to execute the BULK INSERT statement.

Nota

Il percorso di file per l'origine dati è relativo al server.The file path for the data source is relative to the server. Per la corretta esecuzione dell'operazione di copia di massa, è necessario che il processo server abbia accesso a tale percorso.The server process must have access to that path in order for the bulk copy operation to succeed.

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

Vedere ancheSee Also

Operazioni di copia di massa in SQL ServerBulk Copy Operations in SQL Server
Provider gestiti ADO.NET e Centro per sviluppatori di set di datiADO.NET Managed Providers and DataSet Developer Center