Nullable fields when using SELECT...INTO

Hi,

Could anyone tell me what governs whether a column is set as nullable or not nullable when creating a table using SELECT...INTO. It just seems to pick at random for me! I'm quite sure this is not the case. Is there a way to force a column to be non- nullable? I seem to be wasting a lot of time going through and altering the schema so I can use the columns in keys and indexes.

Dave

Normally the meta data from the original table is retrieved, did oyu check that ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

I've checked the source tables and the columns are nullable in there. This is not something I have any control over. Is there no way I can force the columns to be non-nullable in my new table?

Dave

|||YOu will have to put some extra statements after importing the data in the table to change the nullable property of the columns.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

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

1. Any expression that uses a built-in function like SUBSTRING, LEFT, RIGHT etc (except ISNULL) for example is considered as NULLable by the engine. So if you use CAST(somecol as char(8)) then the expression is NULLable

2. Literals, constants, global variables like @.@.DBTS, @.@.ERROR etc are considered non-NULLable since they return some value always

3. If expression is a column then nullability is derived from the source column metadata

So to make an expression or column in the SELECT list not null then use ISNULL around the column or expression.

SELECT isnull(some_null_col, 0) as nc, substring('abc', 1, 1) as null_c1, substring('abc', 1, 1) as non_null_c1

INTO _t

go

exec sp_help '_t'

go

|||

Thanks a lot Umachandar, that's just what I needed to know

Dave