使用 SQL Server SELECT INTO 创建一个可为空的列? [英] Create a nullable column using SQL Server SELECT INTO?
问题描述
当我在 SQL Server 中使用 select into
创建临时表时,有没有办法指定列应该可以为空?我有一个多步骤的过程,我通过选择很多列来制作临时表(这就是为什么我不做 create table #tmp (...)
).创建临时表后,我将更新一些列,其中一些更新可能会使某个字段无效.
When I create a temp table using a select into
in SQL Server, is there a way to specify that a column should be nullable? I have a multi-step process where I'm making a temp table by selecting a lot of columns (which is why I'm not doing a create table #tmp (...)
). After I make that temp table, I'm updating some columns and some of those updates might null out a field.
我知道我可以做一个 alter table alter column
语句来实现我想要的,但我很好奇是否有办法在 select
中指定它本身.我知道您可以内联 cast
列以获得所需的数据类型,但我看不到您如何指定可空性.
I know I could do an alter table alter column
statement to achieve what I want, but I'm curious about whether there's a way to specify this in the select
itself. I know you can inline cast
your columns to get the desired datatype, but I can't see how you specify nullability.
推荐答案
Nullability 继承自源列.
Nullability is inherited from the source column.
您可以使用表达式失去或获得可空性:
You can lose or gain nullability with an expression:
示例(常量文字似乎有问题 - 需要一个可以返回 NULL 的良好的 NOOP 函数):
Example (constant literals appear to be problematic - need a good NOOP function which can return NULL):
CREATE TABLE SO5465245_IN
(
a INT NOT NULL
,b INT NULL
) ;
GO
SELECT COALESCE(a, NULL) AS a
,ISNULL(b, 0) AS b
,COALESCE(10, NULL) AS c1
,COALESCE(ABS(10), NULL) AS c2
,CASE WHEN COALESCE(10, NULL) IS NOT NULL THEN COALESCE(10, NULL) ELSE NULL END AS c3
INTO SO5465245_OUT
FROM SO5465245_IN ;
GO
SELECT TABLE_NAME
,COLUMN_NAME
,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE 'SO5465245%'
ORDER BY TABLE_NAME
,ORDINAL_POSITION ;
GO
DROP TABLE SO5465245_IN ;
GO
DROP TABLE SO5465245_OUT ;
GO
这篇关于使用 SQL Server SELECT INTO 创建一个可为空的列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!