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

1
8
6/30/2013 2:30:26 PM

Accepted Answer

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

8
6/30/2013 2:34:42 PM

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.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow