CODEWITHSUNDEEP
sql-serversql-server-2008csvbulkinsert
James
James

Reputation: 2246

Bulk insert not working for NULL data

When I am inserting bulk data to a table from a CSV file, it is not working, showing error lie :

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 9

Column 9 value in csv file is null..

How do I take care of this?

Upvotes: 2

Views: 4772

Answers (1)

user2299169
user2299169

Reputation:

From this amount of information I'd say the target table's particular field is defined as "NOT NULL". To workaround the issue you have to:

a) modify csv-->add value to field(s) where they have null

b) modify target table by setting affected fields 'nullable':ALTER TABLE [tblName] ALTER COLUMN [nulColName] [varType such as INT] NULL
In case you go for this solution and want to turn back table's status alter it again:
UPDATE [tblName] SET [nulColName]=-1000 WHERE [nulColName] IS NULL to avoid alter errors, then ALTER TABLE [tblName] ALTER COLUMN [nulColName] [varType such as INT] NOT NULL

c) pretty much like 'b' option but a bit more professional and faster: create a temp table based on target table but allowing nulls to any and all fields, then update temp table's null records after csv import with a "default value" and copy the data to the target table


If I'm right about the issue and there's no option to revise csv I'd go for option 'c'

Upvotes: 2

Related Questions