How to alter data type of column has many data exists ("Arithmetic overflow error converting nu-CodePudding

I am using SQL Server 2014. How to alter data type of column has many data exists ("Arithmetic overflow error converting numeric to data type numeric.")

My code

Old data type of field price is REAL. I want it become NUMERIC(10,4) . Production database has many data.

ALTER TABLE motor
ALTER COLUMN price NUMERIC(10,4);

Msg 8115, Level 16, State 8, Line 10
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

Completion time: 2022-04-05T17:03:48.4602172 07:00

How to archive it?

CodePudding user response：

You need to identify rows that don't fit in numeric(10,4) and decide what you want to do with them (fix the values, remove them, or choose a more accommodating type).

SELECT * FROM dbo.motor
  WHERE price IS NOT NULL
    AND TRY_CONVERT(numeric(10,4), price) IS NULL;

We can't tell you the best way to fix the data in your scenario, but you certainly need to make some decision. You can't fit 400 beers in a backpack, you either need to reduce the number of beers, or get a bigger container.

CodePudding user response：

For that, you need to increase the digit in the Numeric data type that is present in the brackets() because you are using the REAL data type for price column, and the size of REAL type data type is 4 bytes.

e.g Numeric(20,4)