Precision Lost while Bulk Inserting records into SQL using SqlBulkCopy

.net bulkinsert sql sqlbulkcopy sql-server-2008

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

Accepted Answer

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


Popular Answer

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.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why