SQL Server Performance Forum – Threads Archive

bulk insert precision lost

Hi all, When bulk inserting from a file into a table, where you have a column of decimal(12,4) the precision is lost as soon as the data gets into the table. BULK INSERT runtime_model_cube
FROM ‘some file .txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘
‘
)
If you do this (below values come from the file): select cast(123547.3521888 as decimal(12,4))
select cast(36.1600999 as decimal(12,4))
select cast(65.6566668 as decimal(12,4))
select cast(368.99999 as decimal(12,4))
select cast(49.555555 as decimal(12,4))
select cast(0.6954752 as decimal(12,4)) Gives you different results from the bulk insert as it looks like its total ignoring the rounding? BULK (incorrect) <-> explicit cast (correct)
123547.3521 <-> 123547.3522
36.1600 <-> 36.1601
65.6566 <-> 65.6567
368.9999 <-> 369.0000
49.5555 <-> 49.5556
.6954 <-> .6955 Any ideas?

I’m not sure if this is the problem, but I think BULK does simply a cut-off, ignoring any rounding rules. A workaround would be to import into a staging table with a VARCHAR(20) or so column and then for there insert into the final table. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

This issue is fixed in SQL 2005 as it now does the correct rounding. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Thanks for the feedback! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

]]>