Precision Lost while Bulk Inserting records into SQL using SqlBulkCopy

Question

I am using the following code to Bulk Insert a data table into my SQL Table:

 // Set up the bulk copy object.  
                using (SqlBulkCopy bulkCopy =
                           new SqlBulkCopy(destinationConnection.Connection))
                {
                    bulkCopy.DestinationTableName =
                        Constants.ReportDataTable;

                    // Write from the source to the destination.
                    DataTable dtBulk = GetDatatableInReportDataFormat(dt, objectName, version);
                    bulkCopy.WriteToServer(dtBulk);//To get the Datatable in the SQL table format

                }

I have a column in my SQL Table named "Value", its type is decimal (28,5). My problem is that some values with decimal numbers are being automaticaly rounded, thus I am losing precison, for example a value of 0.72768 is being saved as 0.72767.

In the Datatable, the column "Value" is of type Double.

Any body has an idea? Thank

Why is the type not "decimal" in the DataTable? Not all numbers can be exactly represents in floating point (the same is true of decimal, but the rounding works more like humans expect, rather than computers) — Marc Gravell, Jun 30 '13 at 14:33
you are right, it is not rounding, but it is not stored as it should — Hassan Mokdad, Jun 30 '13 at 14:35
so the problem i suppose is not that it the values saved are rounded? But are different than the one you want. — kostas ch., Jun 30 '13 at 14:35

score 8 · Accepted Answer · answered Jun 30 '13 at 14:34

8

Make the column in the DataTable as decimal rather than double… I strongly suspect this will make the problem disappear.

answered Jun 30 '13 at 14:34

Marc Gravell

1,026,079
266
2,566
2,900

I tested it on my machine, sql express 2008, and it works fine before setting the column type to decimal, on sql 2008 R2, it is changing th values, I will test on 2008 R2 and get back to you – Hassan Mokdad Jun 30 '13 at 15:00
I have exactly this same problem! – MiBol Sep 12 '13 at 22:33

Kaveh Hadjari · Answer 2 · 2016-01-26T05:48:42.980

The decimal precision of a double is not fixed, even if it's a 128-bit double or higher. So it's not an "rounding error". Simply said a double is not guaranteed to represent all real numbers within a limited decimal point range. This loss of precision increase even more when trying to store bigger numbers or numbers that is very close to zero.

http://en.wikipedia.org/wiki/Double-precision_floating-point_format

If you're storing real numbers that need guarantees about decimal precision like monetary values, longitude/latitude or similar then store them as decimal type.

An uppdate: actually no precision is fixed with double (or single/float) if you store really big numbers then there will be integers you cannot represent because loss off precision and If a calculation should result in such a value then closest representable integer will be calculated and stored instead.

Precision Lost while Bulk Inserting records into SQL using SqlBulkCopy

2 Answers2