table copy misses indexes

[Johnny Ruin]

Hi,

I simply want to create a duplicate of an existing table with a
different name. I've tried the "select * into newtable from
orgtable", which works great, except that it doesn't mark the primary
key field from orgtable in the newtable. I tried creating the
newtable prior to using this select, but then sql server cries about
the table already existing. I tried creating the index after the
select "CREATE UNIQUE INDEX FieldAIndex ON newtable (FieldA)" - that
didn't give a error, but also failed to mark the field as primary.
Suggestions?

[David Portas]

CREATE TABLE newtable (col1 INTEGER, col2 ... etc ... , CONSTRAINT
pk_newtable PRIMARY KEY (col1, col2)) ;

INSERT INTO newtable (col1, col2, ...)
SELECT col1, col2, ...
FROM oldtable ;

Also, take a look at the ALTER TABLE ... ADD CONSTRAINT syntax in Books
Online.

--
David Portas
SQL Server MVP
--

[Johnny Ruin]

That worked, thank you. I know it was uber-newbie ... I just had some
trouble cracking the code. Thanks again.