Assigning column types in select into

Group,

Is there a way to assign nullability on a column when using a select into?
I've tried some of the usual things like coalsce, isnull, and cast. Since
the new table gets definition from the source table or can be somewhat
adjusted with cast is there a way to cast a not null? In the example below
how can I select into causing tableone_new..col2 to be not null. We
typically must use an alter statement after the select into but this seems
inefficient.

Thanks,
Ray

create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
col2
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
goRay (someone@.nowhere.com) writes:
> Is there a way to assign nullability on a column when using a select
> into? I've tried some of the usual things like coalsce, isnull, and
> cast. Since the new table gets definition from the source table or can
> be somewhat adjusted with cast is there a way to cast a not null? In
> the example below how can I select into causing tableone_new..col2 to be
> not null. We typically must use an alter statement after the select
> into but this seems inefficient.

As far as I know there isn't. You might be better off with CREATE TABLE
instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Ray" <someone@.nowhere.com> wrote in message
news:t7tTd.8720$rK1.8198@.newssvr31.news.prodigy.co m...
> Group,
> Is there a way to assign nullability on a column when using a select into?
> I've tried some of the usual things like coalsce, isnull, and cast. Since
> the new table gets definition from the source table or can be somewhat
> adjusted with cast is there a way to cast a not null? In the example
> below how can I select into causing tableone_new..col2 to be not null. We
> typically must use an alter statement after the select into but this seems
> inefficient.

<snip
ISNULL() appears to do what you want (8.00.760 Enterprise Edition), although
COALESCE() does not (don't ask me why). Personally, I agree with Erland - it
would be much better to use CREATE TABLE and therefore make your intentions
clear and your data model explicit.

Simon

create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
isnull(col2, 1) as col2 -- tableone_new.col2 is NOT NULL
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
go

create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
coalesce(col2, 1) as col2 -- tableone_new.col2 IS NULL
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
go|||Simon Hayes (sql@.hayes.ch) writes:
> ISNULL() appears to do what you want (8.00.760 Enterprise Edition),
> although COALESCE() does not (don't ask me why).

There is a similar case with indexed views where you can use isnull()
to make a view indexable but where coalesce() does not cut it. And,
no, I can't really make any sense of it.

(And in the particular case where I looked at it, the view does not
index at all in SQL 2000, but does so in SQL 2005 when you use isnull.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp