Add Column to Temp Table - 'Invalid column name DECIMAL'

Question

I'm creating a temporary table via a SELECT INTO. I'd like to create the temp table then add a column to it like so:

 SELECT id, name, val
 INTO #TEMP_TBL

 ALTER TABLE #TEMP_TBL ADD new_col AS DECIMAL

 Error: Invalid column name 'DECIMAL'.

Where am I going wrong here?

Answer 1

Don't use the AS. The correct syntax is

 ALTER TABLE #TEMP_TBL ADD new_col DECIMAL(18,4) /*Or whatever precision/scale */

Or you could do this all in one go with

SELECT id,
       name,
       val,
       CAST(NULL AS DECIMAL(18, 4)) AS new_col
INTO   #TEMP_TBL 
FROM ....

It thinks you are trying to add a computed column.

CREATE TABLE #TEMP_TBL(X INT)

/* Y is a computed column with same value as X*/
ALTER TABLE #TEMP_TBL ADD Y AS X