Home Dashboard Directory Help
Search

Inaccurate text NULL handling in the topic for BULK INSERT by Erland Sommarskog


Status: 

Resolved
 as Fixed Help for as Fixed


2
0
Sign in
to vote
Type: Bug
ID: 767171
Opened: 10/12/2012 5:29:31 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

The topic for BULK INSERT says (in the section for CHECK_CONSTRAINTS):

UNIQUE, PRIMARY KEY, and NOT NULL constraints are always enforced.

However, this is not accurate. For a string column that is NOT NULL, BULK INSERT inserts a blank string when there is no value in the text file (see the repro below). This is different from BCP, which raises an error in this case.

BULK INSERT has always behaved this way, so changing it seems out of the question. And in
http://connect.microsoft.com/SQLServer/feedback/details/566359/bulk-insert-silently-inserts-empty-string-for-last-column-when-it-is-not-null
it says that the behaviour is by design.

However, the behaviour should be discussed in the topic, and also suggest workarounds if you desire to have an error if there is no value in the input file.

Details
Sign in to post a comment.
Posted by Microsoft on 7/18/2013 at 3:43 PM
The topic has been updated to accurately reflect the NOT NULL behavior. The 2012 topic will be published the week of July 23, 2013. The 2014 topic will be published in a few months.

Kind regards,
Gail Erickson
Posted by Microsoft on 10/26/2012 at 12:51 PM
Hi Erland,
Thank you for posting this issue. I'll review with the PM and update the topic as needed.

Best,
Gail Erickson
SQL Server Documentation team
Sign in to post a workaround.