[SOLVED] How to select data(with one decimal type) from a table and insert into another table (with different decimal type)

How to select data(with one decimal type) from a table and insert into another table (with different decimal type)

I want to select data from TABLE A and insert data into TABLE B. Though I know how to achieve this. I have used:

INSERT INTO TableA(column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM TableB
WHERE condition;

But the challenge is the data type. The target TABLE A has a column with data type definition as

column1(decimal(16,3),null)

Whereas the table TABLE B has a column with data type definition as

column1(decimal(15,4),null)

Please help me here!

Solution

It should be fine. SQL Server will automatically convert the values. If you have a very large value in B that doesn't fit in A, then you could get an error.

To solve that, use try_convert():

INSERT INTO TableA(column1, column2, column3, ...)
    SELECT TRY_CONVERT(decimal(15,4), column1), column2, column3, ...
    FROM TableB
    WHERE condition;

This will avoid a type conversion error.