>>>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.