bulk insert does not read null values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

bulk insert does not read null values

Author

Topic

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-08-21 : 09:43:40

Hi,

This is the table:


CREATE TABLE #temporary2
(
	[TOrderId] [int] NOT NULL,
	[CustodyNr] [int] NOT NULL,
	[InstrumentId] [int] NOT NULL,
	[SellAmount] [decimal](19, 2) NULL
)

This is the Insert:


BULK INSERT  #temporary2
    FROM 'C:\....\temp.rpt' 
    WITH 
    ( 
	--FIRSTROW = 2,
        FIELDTERMINATOR = '\t' 
        ,ROWTERMINATOR = '\n' 
        ,KEEPNULLS
    )

This is the message:
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (SellAmount).

This is from the text file:

12345	15028918	196	NULL

Why can't it read NULL from the text file? When I replace the NULL with nothing the bulk insert works perfectly. But is there any solution to keep the NULL in the text file and then insert the data from text file to table without any problem?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-21 : 10:20:25

NULL is a text. Bulk Insert will have problem converting that to decimal

declare the SellAmount as varchar(30) and import it then replace the text 'NULL' with NULL and convert the value to decimal

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources