adding a unique id and PK to existing tables

Question

i have a few tables that were created in mssql 2005 server. due to my inexperience at the time, the tables were created without a primary key or unqiquely identifying column.

how can i add a column with a unique id (auto incrememnt?) to a table with existing data (around 600k rows)?

i need this to be a Primary key so that i can start using SQL Analysis services.

Many thanks,

Adam

Answer 1

In T-SQL:

ALTER TABLE table_name 
ADD id INTEGER IDENTITY PRIMARY KEY NOT NULL

But as Yaakov says, it's also very easy to do in SSMS.

Answer 2

In T-SQL...

alter table TableName
add ID int identity(1,1) primary key not null

Answer 3

Just add the column through Sql Managemenent studio, set to int, auto-increment (1,1), not null and PK. When you save the table, the column will automatically be added and prepopulated with data.