NOT NULL Constraint Ignored During BULK INSERT

Question

NOT NULL Constraint Ignored During BULK INSERT

lawrence.raiders

SSC Veteran

Points: 227
More actions
June 9, 2011 at 4:15 pm

#252479

NOT NULL Constraint Ignored During BULK INSERT
I am having trouble with a BULK INSERT that is not generating errors when NOT NULL constraints are violated. The table I am loading to is created with the following statement:
CREATE TABLE [ups_Ourkpis].[dbo].[tarun_f1_fctjul11_apr]
(
periodVARCHAR(6)NOT NULL,
scenarioVARCHAR(24)NOT NULL,
cost_centerVARCHAR(24)NOT NULL,
natural_account VARCHAR(24)NULL,
channelVARCHAR(24)NULL,
productVARCHAR(10)NULL,
entityVARCHAR(4)NULL,
locationVARCHAR(24) NOT NULL,
projectVARCHAR(24)NULL,
amountVARCHAR(28)NULL
);
Data is loaded from file to the table via the following:
BULK INSERT [ups_Ourkpis].[dbo].[tarun_f1_fctjul11_apr]
FROM '\\OurServer\wldata\OurKPIs\Inbox\ARUN_F1\arun_f1_fctjul11_apr_ERR_POV.txt'
WITH (KEEPNULLS, CHECK_CONSTRAINTS, ROWTERMINATOR='', ERRORFILE='\\OurServer\wldata\OurKPIs\Inbox\ARUN_F1\arun_f1_fctjul11_apr_ERR_POV.err');
The following 2 rows violate the NOT NULL contraints on cost center and period + scenario, but no error is generated during the BULK INSERT. In fact, after BULK INSERT, the invalid NULLS are converted to empty strings, while the VALID NULLS in the entity column remain NULLs:
YTDM12<T>Fcst11_M3_Local_inp<T><T>X<T>X<T>X<T><T>OTHRUN<T>X<T>90818910-
<T><T>PermInDirEmpl<T>X<T>X<T>X<T><T>OTHRUN<T>X<T>22-
Am I missing something in the CREATE TABLE or BULK INSERT command, or is there something else that I'm missing?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

http://msdn.microsoft.com/en-us/library/ms188365.aspx

CHECK_CONSTRAINTS
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.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

lawrence.raiders SSC Veteran Points: 227 More actions · Answer 2

Crispin, thanks. As indicated in my original post, I am using CHECK_CONSTRAINTS. Am I using CHECK_CONSTRAINTS incorrectly, or does CHECK_CONSTRAINTS ignore the NOT NULL constraint? In my case, no error is generated. NULLs are replaced with empty strings. I do not have this problem with INSERT INTO, which is rejecting NULLs on columns where the NOT NULL constraint is specified.