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.

Savonarola

Possible duplicate of - #5465745 – Han

Will it work if you insert fake record at the very beginning with NULL as value of both columns? – Idel

@Idel Yes, it will work but that's a wrong way to solve this – Savonarola

@Savonarola Do you know correct solution. If so, you can answer on your question thus everybody will know the solution. Or could you please clarify what is wrong in the way which works? Thank you. – Idel

Why the bump? This is already answered in the duplicate link above. – Atalee

@Martin actually it's not, the duplicate link suggest rewriting but I'm looking a way to do it without rewriting – Savonarola

@Idel I don't know the correct solution but inserting a record is not the write way – Savonarola

It isn't possible without rewriting. If you don't create the table explicitly yourself then you just have to accept the way that SQL Server infers the column datatypes. – Atalee

That's sad. I'm upgrading from SQLServer 2000 and I have more than 500 stored procedures with this issue. – Savonarola

I found the link to the duplicated question to have better information and it contained the answer to my issue. This post however did not have the answers I was looking for. - Obi Wan. #5465745 – Mariellamarielle

Answer 1

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

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

Sabina

Answer 2

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

Chiasmus

Answer 3

(Un)fortunately, this question is too popular and appears at the top for Sybase ASE 15.7 as well, so just adding my answer for Sybase here.

For me neither of cast, convert or coalesce worked, but a case statement did (which is what coalesce is, but eh...)

select
    a = case when 1 = 0 then null else 'one' end, 
    b = case when 1 = 0 null else 500 end 
into #temp

Radiothorium

Answer 4

This is an old question but I had a similar issue where I UNION NULLs to the initial query which may have helped the OP.

Select 'one' as a , 500 as b
into #temp
UNION
SELECT NULL, NULL

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

Putting it here so the next time I need to do this and forget how...

Juarez