Create #temp using SELECT INTO with desired data type

Question

For this simple statement:

Select NULL as Col INTO #temp

The default datatype for NULL column is int, if I want to avoid creating # tables ahead and not using table variables, is there any way to default the NULL column as varchar when using SELECT INTO clause?

Thanks

Answer 1

Just CAST it.

Select CAST(NULL AS VARCHAR(30)) as Col 
INTO #temp

There's no way of doing this as an implicit default for bare NULLs.

Answer 2

The answers provided should suffice. I do this a lot; here's a few SELECT INTO techniques that I have found helpful (note my inline comments):

if object_id('tempdb..#t1') is not null drop table #t1;
select 
  someid   = identity(int,1,1),          -- add an identity column 
  someTxt1 = cast('xxx' as varchar(10)), -- set the datatype
  someTxt2 = isnull(cast(555 as int), 0) -- set the column as not nullable
into #sometable;