SqlBulkCopy SqlBulkCopyOptions

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Description

Bitwise flag to specifies one or more options.

// SET SqlBulkCopyOptions value.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.CheckConstraints))
{
	bulkCopy.DestinationTableName = "TheDestinationTable";
	bulkCopy.WriteToServer(dt);
}

Try it

Options

Name Default Description
AllowEncryptedValueModifications No Allow bulk copying encrypting data.
CheckConstraints No Check constraints while data is being inserted.
Default Yes Default options = None.
FireTriggers No Fire triggers while data is being inserted.
KeepIdentity No Preserve source identity values.
KeepNulls No Preserve null values in the destination table regardless of the settings for default values. When not specified, null values are replaced by default values where applicable.
TableLock No Obtain a bulk update lock for the duration of the bulk copy operation. When not specified, row locks are used.
UseInternalTransaction No When specified, each batch of the bulk-copy operation will occur within a transaction.

Remarks

You can specify more than one value by using the "|" operators (Logical OR).

var options = SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.TableLock;

using (var sqlBulk = new SqlBulkCopy(connectionString, options))
{
    sqlBulk.DestinationTableName = "Customers";
    sqlBulk.WriteToServer(dt);
}

Try it

Recommendation

  • SET CheckConstraints ON
  • SET FireTriggers ON

SET CheckConstraints ON

By default, SqlBulkCopy doesn't check constraint.

It's recommended to always check constraint even if your table has none currently.

SET FireTriggers ON

By default, SqlBulkCopy doesn't check trigger.

It's recommended to always fire trigger even if your table has none currently.



Got any SqlBulkCopy Question?