How to create a table using insert so columns are nullable?

Using SQL Server 2008.

Here is a simplified example of my problem:

WITH    cte ( a )
          AS ( SELECT   1
               UNION ALL
               SELECT   NULL
             )
SELECT * INTO a_tmp FROM cte

SELECT * FROM a_tmp

This creates the table c开发者_如何学运维alled a_tmp with a nullable int column.

The next snippet, however creates the table with a non-nullable column:

WITH    cte ( a )
        AS ( SELECT   1
        UNION ALL
        SELECT   2
            )
SELECT * INTO a_tmp FROM cte

How can I change the second snippet to force the table to be created with all its columns nullable?

Make it into a computed expression as generally these get treated as NULL-able unless wrapped in ISNULL()

WITH    cte ( a )
        AS ( SELECT   CAST(1 AS INT)
        UNION ALL
        SELECT   2
            )
SELECT * INTO a_tmp FROM cte

If you want to explicitly define a field as nullable in a table, the right way is to use CREATE TABLE.

As a workaround you can explicitly cast all your fields as their data types, but to me that seems like MORE work than just

CREATE TABLE MyTable (id INT NULL, Otherfield varchar(100)...)

When you use SELECT ... INTO, any expression that uses a built-in function (e.g. CAST) is considered as NULL-able by the engine.

So if you use CAST(somecol as int) then the expression is NULLable.