Create a nullable column using SQL Server SELECT INTO?

Question

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.

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.

Answer 1

Nullability is inherited from the source column.

You can lose or gain nullability with an expression:

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