>>>>What is wrong with CAST(null as int)? Why the Coalesce is necessary?
>>>
>>>Because he wanted to get 0 as the field's value, I assume.
>>
>>CAST() set the field nullable.
>>

>>select CAST(0 as int) as NewColumn
>>into #NewTable
>>
>>insert into #NewTable values (null)
>>select * from #NewTable 
>>

>
>No, CAST alone does not work when I need to select from another table, e.g.
>
>select CAST(cost as money) as cost
>into #newtable
>from myOtherTable
>
>does not work.

sure ?

CREATE TABLE #myOtherTable (cost money not null)

select CAST(cost as money) as cost
into #newtable
from #myOtherTable

INSERT into #newtable VALUES (NULL)

SELECT * FROM #newtable

DROP TABLE #myOtherTable
DROP TABLE #newtable