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

>
>Hmm, yesterday something similar didn't work in my tests, so I resorted to coalesce trick.

CAST() do the job. Your's test is bad somewhere. I'm sure.