Inaccurate text NULL handling in the topic for BULK INSERT

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

Active

Type:	Bug
ID:	767171
Opened:	10/12/2012 5:29:31 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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 (expand)

English

SQL Server 2012 - Enterprise Edition

Documentation

Not Applicable

Compose the file C:\temp\slask.bcp with this content:

||||

Then run:

CREATE TABLE [dbo].[DBAT_STG_COSTCENTERATTR_RF](
    [CO_AREA] [nvarchar](10) NOT NULL,
    [COSTCENTER] [nvarchar](20) NOT NULL,
    [DATETO] [nvarchar](8) NOT NULL,
    [DATEFROM] [nvarchar](8) NULL,
    [BIC_ZINDCTOR] [nvarchar](20) NULL
) ON [PRIMARY]

go
BULK INSERT DBAT_STG_COSTCENTERATTR_RF FROM 'C:\temp\slask.bcp'
WITH(FIELDTERMINATOR='|')
go
select * from DBAT_STG_COSTCENTERATTR_RF
go
drop table DBAT_STG_COSTCENTERATTR_RF

BULK INSERT succeeds in contradiction with the text in BOL.

From the BOL text I would expect the operation to fail, but as noted, it is likely that the documentation needs to be clarified.

0 attachments

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