Contents Exit focus mode. Please rate your experience Yes No. Any additional feedback? Namespace: System. SqlClient Assembly: System. Lets you efficiently bulk load a SQL Server table with data from another source. Important This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. In this article. SqlBulkCopy SqlConnection. SqlBulkCopy String.
Defines the number of rows to be processed before generating a notification event. Equals Object. Please rate your experience Yes No. Any additional feedback? Submit and view feedback for This product This page. View all page feedback. In this article. The following string s can be used as option s in the bulk copying of data into a table. Bulk copy performance is improved if the data file being loaded is sorted according to the clustered index on the table.
The server optimizes the bulk load according to the value bb. This option significantly improves performance because holding a lock only for the duration of the bulk copy operation reduces lock contention on the table. A non-XML format file is an ordinary file with a well-defined structure, and contains metadata about the column such as storage type, prefix length, field length, and field terminator.
If -f is used with the format option, the specified format file is created for the specified table or view. To create an XML format file, specify the -x option. To skip a table column, modify the format file definition of the corresponding row and set the values to 0 for all the related columns as shown below. Most of the options in the BCP command are case sensitive. It is recommended that we assume everything as case sensitive, so that we never run into troubles using the command.
Sometimes we need to delete the constraint and dump the data, and then re-add those deleted constraints. By default, a bulk copy operation is its own transaction.
When you want to perform a dedicated bulk copy operation, create an instance of SqlBulkCopy with a connection string, or use an existing SqlConnection object without an active transaction. The bulk copy operation creates a transaction in each scenario and then commits or rolls back the transaction. You can explicitly specify the UseInternalTransaction option in the SqlBulkCopy class constructor to execute a bulk copy operation in its own transaction.
Each batch of the operation will execute within a separate transaction. Since different batches are executed in different transactions, if an error occurs during the bulk copy operation, all the rows in the current batch will be rolled back, but rows from previous batches will remain in the database.
The following console application is similar to the previous example, with one exception: In this example, the bulk copy operation manages its own transactions.
In this situation, the bulk copy operation is done in the existing transaction, and no change is made to the transaction state - it is not committed or aborted. This method allows an application to include the bulk copy operation in a transaction with other database operations. However, if you do not specify a SqlTransaction object and pass a null reference, and the connection has an active transaction, an exception is thrown.
If you need to roll back the entire bulk copy operation because an error occurs, or if the bulk copy should execute as part of a larger process that can be rolled back, you can provide a SqlTransaction object to the SqlBulkCopy constructor.
The following console application is similar to the first non-transacted example, with one exception: in this example, the bulk copy operation is included in a larger, external transaction.
0コメント