如何允许临时表接受空值 [英] How to allow temporary tables to accept null values

问题描述

如果您在 SQL Server 中使用插入到"创建临时表，它将使用第一个插入来确定列是否接受空值.如果第一个插入具有空值，则该列变为可为空的，否则它将不可为空.

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?

示例

这没有任何问题

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

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

但是这会抛出无法将 NULL 值插入列 '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

我知道我可以执行 create Table 或 alter column 语句，但我想在不重写数百个现有查询的情况下做到这一点.

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

推荐答案

这个怎么样?

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

这篇关于如何允许临时表接受空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！