BULK INSERT & CHECK Constraints

There are a few occasions where BULK INSERT seems to be a great solution for loading data from flat files. Sure, there are alternatives. You could also use BCP or SSIS, but BULK INSERT can come in handy in a time crunch. I don’t use this too often, but in the right circumstances, this could be one of the fastest ways to load data. While looking at the many optional arguments for this statement, I noticed an interesting one: CHECK_CONSTRAINTS. Let’s find out what this is all about.

Documentation

Books Online states the following relating to this argument:

Specifies that all constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS option, any CHECK and FOREIGN KEY constraints are ignored, and after the operation, the constraint on the table is marked as not-trusted.

That’s pretty straight forward. Let’s try it out to be extra sure.

Prep Work

Let’s create a random “Staging” table to store our results. In the definition of that table, I’ll put a CHECK constraint on the “TransactionDate” column. That constraint should prevent any future transactions from being inserted.

 CREATE TABLE dbo.MadeToStage
(
	TransactionDate date NOT NULL
		CONSTRAINT CK_TransactionDate CHECK (TransactionDate <= CURRENT_TIMESTAMP),
	TransactionAmount decimal (13, 2)
);

We can also generate a small sample file to demonstrate. I created a CSV and placed it out in the temp folder in my C: drive. Mine looks like this.

Real “Fake” Work

That’s all I need to try out the BULK INSERT.

BULK INSERT dbo.MadeToStage
FROM 'C:/temp/transactions.csv'
WITH (FIRSTROW = 2, 
      FORMAT = 'CSV');

I used FIRSTROW = 2 as the first row in my flat file was a header.

After executing that statement, I see 10 rows affected. Sure enough, even though it’s not yet April 18th, the row made it in. To make matters worse, this constraint is no longer trusted. It still works, at it isn’t disabled, but there is now a potential for performance degradation. In order to cause the BULK INSERT to fail, we would need to include the CHECK_CONSTRAINTS argument.

BULK INSERT dbo.MadeToStage
FROM 'C:/temp/transactions.csv'
WITH (FIRSTROW = 2,
      CHECK_CONSTRAINTS,
      FORMAT = 'CSV');

How can we ever trust it again?

First, we would need to either remove or update the rows that exist in the table that in violation of this constraint. Once this is completed, we should run the following statement:

ALTER TABLE dbo.MadeToStage 
WITH CHECK CHECK CONSTRAINT CK_TransactionDate;

Check 1, Check 2. CHECK needs to be there twice.

I guess we could also just wait a few days for that to be valid as well.

Conclusion

To summarize this, I guess we should all be taking a closer look at the documentation. If this happened once and went unnoticed on a “real” table, we might have bigger problems. It would be tough to catch the untrusted constraint without proper procedures in place. Phil Factor also has a great article on Simple Talk titled The Whys and Wherefores of Untrusted or Disabled Constraints that can shine some more light on this subject.

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *