How to allow temporary tables to accept null values
null, sql, sql-server-2008-r2, temp-tables
Solution
I would workaround this by explicitly creating temporary table before first insert.
create table #temp (a varchar(10) not null, b int null)
Problem
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.