Access double to SQL decimal using SQL Bulk Copy

ms-access sql sqlbulkcopy vb.net

Question

I have an Access database that I am converting to an SQL database using sqlbulkcopy in vb.net. We have currency values (up to 2 decimal places) stored in Access as Double. When I convert these Access fields to SQL fields with type [decimal] (19, 2), a number like 81.6 stored in the Access field sometimes appears as 81.59 in the SQL field when converted.

If I change the type in SQL to [decimal] (19,4) then it shows as 81.5999. The number in Access is definitely calculated and stored to no more than two decimal places. Can someone explain to me why SQL is doing this? It doesn't happen all the time... only in some records.

I've reviewed just about any article I could find that explains decimal types and I understand what decimal is (I think), but I don't understand why numbers sometimes get converted like this. Thanks for your help.

EDIT: Here is the code that I mentioned below that I used to loop through all of the tables and columns to rename the aliased columns ending in "1Z" to their original names:

        Dim dtTbls As DataTable = cnnSQL.GetSchema("Tables")
    For Each drTbls As DataRow In dtTbls.Rows
        Dim sTableName As String = drTbls(2)
        Dim query As String = String.Concat("select * from [", sTableName, "] Where 0=1")
        Dim myCmd As SqlDataAdapter = New SqlDataAdapter(query, cnnSQL)
        Dim myData As New DataSet()
        myCmd.Fill(myData)
        For Each colu As DataColumn In myData.Tables(0).Columns
            If colu.ColumnName.EndsWith("1Z") = True Then
                Dim sNewColName As String = colu.ColumnName.Remove(colu.ColumnName.Length - 2, 2)
                dbSQL.ExecuteNonQuery(String.Concat("EXEC sp_rename '", sTableName, ".", colu.ColumnName, "', '", sNewColName, "', 'COLUMN'"))
            End If
        Next colu
    Next drTbls

Accepted Answer

Your problem is that floating-point types like Double often cannot store numbers exactly, even values which seem to be quite "simple" numbers to us humans. This can be demonstrated by typing the following into the Immediate Window of the VBA editor in Access:

?(CDbl(81.60) * CDbl(100)) - CDbl(8160)

The result returned is

-5.6843418860808E-13

Your experience suggests that a direct transfer of Access Double to SQL Server decimal will truncate the number instead of rounding it. Therefore, what you need to do is copy the values from Access into a separate SQL Server column of type float, and then use an UPDATE query in SQL Server to populate the decimal(19,2) column, e.g.,

UPDATE TableName SET DecimalColumn = ROUND(FloatColumn, 2)

Edit

In case it makes things easier, it should also be possible to do the rounding and conversion on the Access side. You could create a Select query for each table that rounds the monetary values and converts them to Currency, like this...

SELECT ProductID, ProductName, CCur(Round([UnitPrice],2)) AS CurrUnitPrice
FROM Products;

...and then SqlBulkCopy the query instead of the table.

Edit

As you may have discovered, trying to convert a column while retaining the original column name with a query like...

SELECT ProductID, ProductName, CCur(Round([UnitPrice],2)) AS UnitPrice
FROM Products

...results in the error

Circular reference cause by alias 'UnitPrice' in query definition's SELECT list

A workaround for that issue would be to use a query like this:

SELECT ProductID, ProductName, CurrUnitPrice AS UnitPrice
FROM
(
    SELECT ProductID, ProductName, CCur(Round([UnitPrice],2)) AS CurrUnitPrice
    FROM Products
)



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