SQL Server Bulk Insert – Part 2

SQL Server Bulk Insert – Part 2
Rate this post

In the previous part of this article, we discussed how to import CSV files to SQL Server with the help of BULK INSERT statement. We discussed the main methodology of bulk insert process and also the details of BATCHSIZE and MAXERRORS options in scenarios.  In this part, we will go through some other options (FIRE_TRIGGERS, CHECK_CONSTRAINTS and TABLOCK) of bulk insert process in various scenarios.

Scenario 1: Can we enable triggers in the destination table during the bulk insert operation?

By default, during the bulk insert process, the insert triggers which are specified in the target table are not fired, however, in some situations we may want to enable these triggers. A solution to this issue is to use FIRE_TRIGGERS option in bulk insert statements. I want to add a notice that this option can affect and decrease the bulk insert operation performance because trigger/triggers can make separate operations in the database. In the following sample, we will demonstrate this.  At first, we will not set the FIRE_TRIGGERS parameter and the bulk insert process will not fire the insert trigger.  In the following T-SQL script, we will define an insert trigger for the Sales table.

Insert trigger

As you can see above, the insert trigger did not fire because we did not set the FIRE_TRIGGERS option. Now, we will add the FIRE_TRIGGERS option to the bulk insert statement so that this option enables to insert a fire trigger.

fire trigger

Scenario 2: How can enable a check constraint during the bulk insert operation?

Check constraints allow us to enforce data integrity in SQL Server tables. The purpose of the constraint is to check inserted, updated or deleted values according to their syntax regulation. Such as, the NOT NULL constraint provides that a specified column cannot be modified by the NULL value. Now, we will focus on constraints and bulk insert interaction. By default, during bulk insert process any check and foreign key constraints are ignored, but this option has some exceptions. According to the Microsoft documentation “UNIQUE and PRIMARY KEY constraints are always enforced. When importing into a character column  for which the NOT NULL constraint is defined, BULK INSERT inserts a blank string when there is no value in the text file.” In the following T-SQL script, we will add a check constraint to the OrderDate column which controls the order date greater than 01.01.2016.

check

As you can see in the above sample, the bulk insert process skips the check constraint control. However, SQL Server indicates check constraint as not-trusted.

is not trusted

This value indicates that somebody inserted or updated some data to this column by skipping the check constraint, at the same time this column may contain inconsistent data with reference to that constraint.  Now, we will try to execute the bulk insert statement with the CHECK_CONSTRAINTS option. The result is very simple, check constraint returns an error because of improper data.

check constraint

Scenario 3: How to increase performance in multiple bulk inserts into one destination table?

The main purpose of the locking mechanism in SQL Server is to protect and ensure the integrity of data. In the Main concept of SQL Server locking article, you can find details about lock mechanism. Now, we will focus on bulk insert process locking details. If you run the bulk insert statement without the TABLELOCK option, it acquires the lock of rows or table according to lock hierarchy. However, in some cases, we may want to execute multiple bulk insert processes against one destination table, so we can decrease the operation time of the bulk insert. At first, we will execute two bulk insert statements simultaneously and analyze the behavior of the locking mechanism.  We will open two query windows in SQL Server Management Studio and run the following bulk insert statements simultaneously.

bulk insert

When we execute the following dmv (Dynamic Management View) query, which helps to monitor the status of the bulk insert process.

As you can see in the above image, session 61, the bulk insert process status is suspended due to locking. If we verify the problem, session 59 locks the bulk insert destination table and session 61 waits for releasing of this lock to continue the bulk insert process. Now, we will add the TABLOCK option to the bulk insert statements and execute the queries.

tablock

status running

When we execute the dmv monitoring query again, we cannot see any suspended bulk insert process because SQL Server uses a special lock type  called bulk update lock (BU). This lock type allows to process multiple bulk insert operations against the same table simultaneously and this option also decreases the total time of the bulk insert process.

When we execute the following query during the bulk insert process, we can monitor the locking details and lock types.

Locking details

Conclusion

In this article, we explored all details of bulk insert operation in SQL Server. Particularly, we mentioned BULK INSERT command and its settings and options, and we also analyzed various scenarios which are close to real life problems.

 

References

BULK INSERT (Transact-SQL)

Prerequisites for Minimal Logging in Bulk Import

Controlling Locking Behavior for Bulk Import

 

Esat Erkeç

Esat Erkeç

Esat Erkec is an SQL Server professional that began his career as a Software Developer over 8 years ago. He is an SQL Server Microsoft Certified Solutions Expert. Most of his career has focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence.
Esat Erkeç

Latest posts by Esat Erkeç (see all)