How is the SqlBulkCopy usage in SQL Server?

In SQL Server, the SqlBulkCopy class is used to efficiently load a large amount of data quickly from one data source to another. It can copy data from one table to another, or copy data from other sources such as DataTable and DataReader to SQL Server tables.

Here is an example of how to use the SqlBulkCopy class.

1. Create a SqlBulkCopy object, and set the connection string and table name for the target table.

string connectionString = "Data Source=(local);Initial Catalog=YourDatabase;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "YourDestinationTable";
        
        // 设置其他选项，如BatchSize和Timeout等
        bulkCopy.BatchSize = 100;
        bulkCopy.BulkCopyTimeout = 60;
        
        // 将数据从源表复制到目标表
        bulkCopy.WriteToServer(yourSourceDataTable);
    }
}

1. In the above code, yourSourceDataTable is a DataTable object containing data that can be populated in various ways.
2. Other options, such as CheckConstraints, FireTriggers, can be set using the SqlBulkCopyOptions enumeration.
3. You can use the ColumnMappings property to map columns from the source table to the target table, ensuring that data is copied correctly into the target table. For example:

bulkCopy.ColumnMappings.Add("SourceColumn1", "DestinationColumn1");
bulkCopy.ColumnMappings.Add("SourceColumn2", "DestinationColumn2");
// ...

It is important to note that the SqlBulkCopy class improves performance significantly by using batch operations when copying data, particularly when dealing with large amounts of data. Additionally, when using the SqlBulkCopy class, the structure of the target table must be compatible with the structure of the source table to avoid errors.