Sql Bulk Copy Truncating Decimal

Question

When I insert decimal values into Sql Server 2005 from a C# DataTable using bulk copy the values get truncated instead of rounded.

• The data type in the DataTable is Decimal.
• The data type in the database is Decimal(19,3)
• The value in the DataTable is 1.0005
• The value insert in the database is 1.000 (I expected 1.001)

The code I'm using is pretty simple:

var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, null) { DestinationTableName = tableName};
bulkCopy.WriteToServer(dataTable);

Does anyone know how to fix this?

Answer 1

According to the reference source, SqlBulkCopy always truncates decimal values instead of rounding, which unfortunately differs from the behavior of the BULK INSERT statement.

The private ConvertValue method calls TdsParser.AdjustSqlDecimalScale if the scale of the source value differs from the scale of the destination column:

switch(type.NullableType) {
    case TdsEnums.SQLNUMERICN:
    case TdsEnums.SQLDECIMALN:
        // ...

        if (sqlValue.Scale != metadata.scale) {                            
            sqlValue = TdsParser.AdjustSqlDecimalScale(sqlValue, metadata.scale);  
        }

AdjustSqlDecimalScale in turn calls SqlDecimal.AdjustScale, passing false for fRound:

static internal SqlDecimal AdjustSqlDecimalScale(SqlDecimal d, int newScale) {
    if (d.Scale != newScale) {
        return SqlDecimal.AdjustScale(d, newScale - d.Scale, false /* Don't round, truncate.  MDAC 69229 */);
    }

    return d;
}

There's apparently no way to override this behavior and pass true to AdjustScale, so if you want to use SqlBulkCopy, you will need to round the values in the DataTable yourself before calling WriteToServer.

Alternatively, you could write the data to a file and execute BULK INSERT directly, forgoing SqlBulkCopy.