Create Primary Key for Select into ..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Create Primary Key for Select into ..

Author

Topic

kaus
Posting Yak Master

179 Posts

Posted - 2002-04-15 : 16:54:23

Is there any way to create a primary key when creating a new table with the Select Into statement ?? The table needs to have an autoincrement primary key. The table is dropped each time and then re-created with new data in a stored procedure

Thanks

Pete

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-04-15 : 17:08:45

Not while doing the SELECT INTO, but if you alter the table and add a column with autoincrement, it should populate everything for you:

SELECT
*
INT0
tblWhatever
FROM
tblWhatever2

ALTER TABLE tblWhatever
ADD ID INT IDENTITY(1,1)

*************************
Just trying to get things done

kaus
Posting Yak Master

179 Posts

Posted - 2002-04-15 : 17:22:00

Thanks

Pete

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-16 : 08:58:09

I hate to be the know-it-all-noobie here, but ...


use pubs
go
select
    identity(int,1,1) as OrderID,
    au_lname
into
    #au_lname_desc
from
    authors
order by
    au_lname desc
go
select
    OrderID,
    au_lname
from
    #au_lname_desc
go

... there is no need to add the column later, you can do it all in one shot.

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources