Copy table + new PK

Question

Hello,

I have a table without a PK. The table has about 500 rows so I don't want to write them manually. What's the best way to add a PK?

Thank you,

Rafa

Answer 1

ALTER TABLE T ADD PkColumn INT NOT NULL IDENTITY
ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (PkColumn)

Answer 2

Hm.. simple program which reads all records and inserts them to new table with PK?

Answer 3

ALTER TABLE CurrentTable ADD pkNewColumn INT IDENTITY (1,1) NOT NULL
ALTER TABLE CurrentTable ADD CONSTRAINT PK_pkNewColumn PRIMARY KEY (pkNewColumn)

Answer 4

Depends on if your adding a new column that will be a primary key or if you want to designate an existing column as a primary key. To modify an existing column:

Create a clone of your current table, and backup the 500 rows using a

INSERT INTO BACKUP_TABLE ( COLA, COLB, COLC ) SELECT COLA, COLB,COLC FROM TABLE;

Then recreate your original table with the primary key, and copy the backup table into the original using a similary SQL statement as above.