Creating a column as NOT NULL with "select into"

I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.
Try:
create table t
(
ID int identity primary key
, x char (2) null
)
insert t (x) values ('XX')
insert t (x) values ('YY')
go
select
ID
, isnull (x, '--') as y
into
x
from
t
exec sp_help x
go
drop table t, x
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.
|||TJ,
A general recommendation is to create the table using DDL.
CREATE TABLE NewTable
(col1 int,
col2 varchar(100)0
go
INSERT INTO NewTable SELECT col1, COALESCE(col2, '') FROM OldTable
go
SELECT INTO is best used for the temporary quick and dirty operation, not
for when you want something to stick around.
Russell Fields
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:##97VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>
|||That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>
|||Your welcomes. ;-)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23pCzI8iWEHA.2852@.TK2MSFTNGP12.phx.gbl...
That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>