How to Change datatype of a Column in a temporary table in SQL

sql-server, t-sql, temp-tables

Solution

Just explicitly cast the numeric as a larger type. I'm using 18,2 as an example, but I don't know your data, so different precision / scale may work better for you:

SELECT TableA.ID_NO, 
  SUM(CONVERT(DECIMAL(18,2), TABLEA.COST) * TABLEB.QTY) as TOTAL
INTO #matCalc
FROM TABLEA A 
INNER JOIN TABLEB 
ON A.ID_NO = B.ID_NO;

Problem

Is there any way to change(increase) the data type of a column while saving the values into a temporary table? ``` SELECT TableA.ID_NO, sum(TABLEA.COST * TABLEB.QTY) as TOTAL INTO #matCalc FROM TABLEA A INNER JOIN TABLEB ON A.ID_NO = B.ID_NO ``` We have a bigger calculation in the actual query. When we execute our stored proc we get an error "arithmetic overflow error converting numeric to data type numeric". Any solution is greatly appreciated. Thank you.

Original source