1

I am using BulkCopy to insert data into SQL table. However for few rows values don't match those in DataTable.

DataTable Data = myExcelSource.ToDataTable();
bulkCopy.WriteToServer(Data);

If in Data column value is 17.85, then insert goes with 17.8500, but if value is 17.86 then insert goes 17.8599.

Did anyone face such issue and know the cause?

Some other values that fail to insert: 32.04; 44.65; 19.06;

Edit1: SQL Table column data type is decimal(15, 4).

Michał Woliński
  • 346
  • 2
  • 12
  • 1
    The column in the database is of type `double` (or `float` or similar), right? It sounds like you run into the "problem" with the [floating point format](https://en.wikipedia.org/wiki/Double-precision_floating-point_format) (It's not _really_ a problem. These types are great for measuring physical things for example. But they are very "bad" for financial stuff like prices). If you need decimal precision, use the `decimal` type. – Corak May 15 '18 at 09:57
  • Okay, and the datatype you're working with in code? (before you set the column value) -- "ExcelSource" - maybe it is read as a double/float from there? – Corak May 15 '18 at 10:01
  • @Corak Yes, I checked it. In DataTable it is `double`, in SQL `decimal(15,4)`. Switched to `real` in SQL, and in moved glitch to different rows, but still appears. – Michał Woliński May 15 '18 at 10:06
  • @Corak Float fails even more: 11,6998996734619 instead of 11.70 – Michał Woliński May 15 '18 at 10:07
  • 2
    both `double` and `real` are floating point. It seems like you're interested in "exact" numbers, so I'd urge you to use `decimal` everywhere. So the problem doesn't seem to be on the SQL side, but more on the Excel side. Sadly I don't really know how you could convince Excel do give you `decimal` values. – Corak May 15 '18 at 10:09
  • @Corak You were somehow correct. Issue was in datatypes, on excel side. Excel returned value as `double`. Forcing it to `decimal` did the trick. No idea why. – Michał Woliński May 15 '18 at 10:14
  • 1
    If you use `decimal` *everywhere*, you always get the precise value, at the cost of it being a bit slower. If you use `double` at some point, you lose precision, because not all values can be represented exactly in this format, but it's a bit faster. See also [When should I use double instead of decimal?](https://stackoverflow.com/a/803260/1336590) and especially [Jon Skeet on Floating Point](http://csharpindepth.com/Articles/General/FloatingPoint.aspx) and [Jon Skeet on Decimal](http://csharpindepth.com/Articles/General/Decimal.aspx). – Corak May 15 '18 at 10:21

0 Answers0