CODEWITHSUNDEEP
sqlsql-serverdatenullable
Revanth Kumar
Revanth Kumar

Reputation: 837

SQL Server not allowing NULL for Date even though mentioned as null while creation

I am trying to create a table for library management system and i want my date_in to be null for some cases. When I am trying to give null it is giving me error as:

Msg 4864, Level 16, State 1, Line 7
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 7 (date_in).

Msg 4864, Level 16, State 1, Line 7
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 7 (date_in).

Msg 4864, Level 16, State 1, Line 7
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 7 (date_in).

Msg 4864, Level 16, State 1, Line 7 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 7 (date_in).

Msg 4864, Level 16, State 1, Line 7
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 7 (date_in).

Msg 4864, Level 16, State 1, Line 7
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 13, column 7 (date_in).

I have explicitly mentioned that attribute to take null but still it is not allowing. help me

thanks in advance

Here is the query:

create table book_loans
(
     loan_id varchar(10) not null,
     book_id varchar(10) not null,
     branch_id smallint not null,
     card_no varchar(10) not null,
     date_out date null default null,
     due_date date null default null,
     date_in date null default null
)

BULK
INSERT book_loans
FROM 'F:\sql project\resources\SQL_library_project_data\book_loans_data.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '<>',
FIRSTROW=2
)
GO

here is the data file:

load_id,book_id,branch_id,card_no,date_out,due_date,date_in<>
1,0399147020,1,9019,2013-11-22,2013-12-06,2013-12-01<>
2,0030059380,4,9007,2013-12-01,2013-12-15,2013-12-16<>
3,0671880756,5,9018,2013-12-08,2013-12-22,2013-12-22<>
4,0911625291,3,9013,2014-01-02,2014-01-16,2014-01-12<>
5,0688161995,5,9022,2014-02-10,2014-02-24,2014-03-01<>
6,0911625291,2,9011,2014-03-03,2014-03-17,2014-03-16<>
7,1861003730,3,9034,2014-04-17,2014-05-01,NULL<>
8,0201612585,3,9034,2014-04-17,2014-05-01,NULL<>
9,1565927699,3,9034,2014-04-17,2014-05-01,NULL<>
10,0192860925,4,9009,2014-04-18,2014-05-02,NULL<>
11,0805057579,1,9021,2014-04-18,2014-05-02,NULL<>
12,0911625607,2,9018,2014-04-19,2014-05-03,NULL

Upvotes: 1

Views: 2456

Answers (2)

artm
artm

Reputation: 8584

The way BULK INSERT works is that a NULL value needs to be an empty field, i.e. value,2,,3. Two delimeters without a value in between declares null value. In your file, you're using NULL to indicate DB NULL value but there's no way for bulk insert to read that as a DB NULL value because you could be trying to insert string 'NULL' in to the field. You can either remove all NULL s from your text file, or if that's not the option you can bulk import to a temp table where the last column is defined as varchar and after the import if the value of last column is 'NULL' then use a DB null value.

Also see http://msdn.microsoft.com/en-au/library/ms162802.aspx if you want to produce a text file from sql DB.

Upvotes: 2

TeamTam
TeamTam

Reputation: 1608

BULK
INSERT book_loans
FROM 'F:\sql project\resources\SQL_library_project_data\book_loans_data.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '<>',
FIRSTROW=2,
KEEPNULLS
)

Upvotes: 1

Related Questions