问题描述
如果您在 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
这篇关于如何允许临时表接受空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！