sql server programming: Target environment : MS-SQL SERVER 2005 Standard Edition with SP1 [Ver. -
9.00.1399.06]

I have a table created as shown below.
create table Blk_Test_Tbl
(
Col1 bigint not null ,
Col2 varchar(100) not null ,
Col3 varchar(100) null
)
go

Note that the Col1 & 2 are NOT NULL columns.
The first column is BIGINT and other two columns are VARCHAR fields

I have a character data file ("test_data.txt") in which column values are
delimited with "|" (pipe) symbol.
The content of the data file is as follows.
1|one|one
2||two
4|four|
5|five|five
|three|three

Note that in the data file, the second row's second column's value is NULL.
The third row's third column's value is NULL.
The last row's first column value is NULL.


I have a format file ("test_data.fmt") with content as shown below.
9.0
3
1 SQLCHAR 0 0 "|" 1 COL1 ""
2 SQLCHAR 0 0 "|" 2 COL2 ""
3 SQLCHAR 0 0 "\r\n" 3 COL3 ""

When I run the following bulk insert statement on this table, I am expecting
an exception been thrown by SQL Server while trying to insert NULL value in
to NOT NULL columns.

BULK INSERT parent FROM 'D:\test_data.txt'
WITH (BATCHSIZE=10, KEEPNULLS, MAXERRORS=0, FIELDTERMINATOR='|',
ROWTERMINATOR='\n', FORMATFILE='D:\test_data.fmt')


Instead, the bulk insert passes without any error. The records that got
inserted are as shown below.
== ==== ====
sno descr descr1

== ==== ====
1 one one
2 two
4 four NULL
5 five five
0 three three

Note that the second row's second column value in the data file was NULL but
it got inserted as an empty string as the column is NOT NULLable.
The third row's third column's value was NULL in the data file and got
inserted as NULL itself because it is NULLable column.
The last row's first column's value was NULL in the data file, but got
inserted as "0" as it was BIGINT column with NOT NULL Constraint on it.

From this, it seems, the SQL Server internally changes the data values of
NOT Null columns just to ensure that the BULK insert happens successfully.
But it should actually throw an exception.

For NOT NULL columns with VARCHAR datatype, it is inserts an empty string
instead of NULL.
For NOT NULL columns with BIGINT data type, it is inserts a "0" instead of
NULL.

My questions are:
1). Did I miss anything in the format file to avoid any internal data
conversion for NOT NULL columns?
2). Or is this a bug with BULK INSERT statement in SS-2K5?
3). Or is this the way the BULK INSERT works? If yes, how can i over come
this as i need to stop execution of Blk insert when there is any NULL value
in the data file for the NOT NULL columns?

Rijesh (Rijesh@discussions.microsoft.com) writes:
[quoted text, click to view]

1399 is RTM, it is not SP1.

[quoted text, click to view]

I examined this, and it appears that replacing the empty string in
the host data file with the empty string is what BULK INSERT always do.
This happens in SQL 2005 and SQL 2000, with and without format file.
While questionable, it can be acceptable in some scenarios.

The 0 for the missing value in the bigint field is definitely wrong,
and I found that this does not happen on SQL 2000, and it happens on
SQL 2005, if you use a format file. I've filed a bug for this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackI
D=272878

To get the functionality you desire, there are two options:
1) Use BCP. BCP cries foul and does not replace with some other value.
2) Use OPENROWSET(BULK). This is the same engine as BULK INSERT, but
you can modify the values. At least this is workable with the
empty strings. Less with the zeroes, as there actually may be
zeroes in the file!



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Thanks Erland for your reply.

[quoted text, click to view]