How to allow temporary tables to accept null values

Question

If you create temp tables using "insert into" in SQL Server it uses the first insert to determine whether a column accepts null value or not. if the first insert has null value the column become nullable otherwise it will be non-nullable.

Is there a way to create temp tables using "insert into" to accept null values?

Example

This works without any problem

Select 'one' as a , null as b
into #temp

insert into #temp
Select 'two' as a , 500 as b

However this throws "Cannot insert the value NULL into column 'b'"

Select 'one' as a , 500 as b
into #temp

insert into #temp
Select 'two' as a , null as b

I know I could do create Table or alter column statement but I want to do it without rewriting hundreds of the existing queries.

Solution 1

How about this?

Select CONVERT(varchar(100), 'one') as a , CONVERT(int, 500) as b
into #temp

insert into #temp
Select 'two' as a , null as b

select * from #temp order by 1

Solution 2

I would workaround this by explicitly creating temporary table before first insert.

create table #temp (a varchar(10) not null, b int null)