Converting NVARCHAR to DECIMAL: All decimals lost

Question

I am loading data from a CSV file to a staging table (using BULK INSERT) where all column types are NVARCHAR(100). This works well.

The idea is then to insert that data into the productive table and while doing that changing the data types.

When trying to convert a column with numeric values from NVARCHAR to DECIMAL, all decimals are gone.

Create and insert from staging table to production table:

DROP TABLE IF EXISTS [dbo].[factFinanzbuchhaltung] 
GO

CREATE TABLE [dbo].[factFinanzbuchhaltung] 
( 
    Wert DECIMAL 
) 

GO 
INSERT INTO [dbo].[factFinanzbuchhaltung] 
SELECT CONVERT(DECIMAL(25, 2), ROUND(Wert,2))
FROM [dbo].[Stage_factFinanzbuchhaltung]

how the data looks before and after conversion

What am I doing wrong? I feel like I tried very combination of CONVERT, CAST and number of decimals. With our without rounding.

Answer 1

Decimal is a fixed point number, which has a declared number of decimals (a.k.a. scale). When you declare a column as type DECIMAL, you get a decimal with precision 18 and scale 0 (source). In other words, it can only store integer values (whole numbers), and drops anything after the decimal point.

You need to declare with the desired number of decimals, e.g. DECIMAL(18, 2) for two decimals. A quick look at your screenshot suggests you need DECIMAL(18, 8). The only other option would be to use FLOAT (double precision), but that could lead to loss of precision. In some database systems you also have a DECFLOAT (decimal floating point) type, but SQL Server does not have this type.