Geek asks and answers

How to control uncertainty in SELECT INTO for columns with letters

I notice that when I use this operator, the column is Actionnot NULL:

SELECT TOP 0 SerialNumber, 0 [Action] INTO #MyTable FROM FSE_SerialNumber

But when I use this operator, the column Actionis NULL:

SELECT TOP 0 SerialNumber, CAST(0 as int) [Action] INTO #MyTable FROM FSE_SerialNumber

My reason for creating a table this way is that I do not want the temporary table to inherit the SerialNumber sort from the server's default sort setting or elsewhere. I want it to match the FSE_SerialNumber..SerialNumber mapping.

My question is, can I rely on a translation function that gives me columns with a null value, or is this explicitly undefined and can change. Why does a cast unexpectedly cause a column to nullify? Is there a better way (besides comments) to make it clear that my intention is to get a column with a null value?

+5
source share
3 answers

The final answer seems to be here . Copy here:

Metadata is determined based on the source column and the expressions used in the SELECT list. The following are the rules:

  • , , SUBSTRING, LEFT, RIGHT .. ( ISNULL), , NULLable . , CAST (somecol char (8)), NULLABLE

  • , , , @@DBTS, @@ERROR .. -NULLable,

  • - , nullability

    , SELECT , ISNULL .

, , CAST.

+5

. :

Create Table #MyTable (
   SerialNumber int Not Null, -- or however this is correctly defined.
   Action int Null
)

Insert Insert... Select From...

Insert Into #MyTable(SerialNumber, Action)
Select SerialNumber, 0
From FSE_SerialNumber

, .

, , , -, .

0

A quick general hint for SELECT ... INTO: use ISNULL(with a column and it will be created as a NOT NULL column in the table.

0
source

More articles:


All Articles