Add a column to existing table and uniquely number them on MS SQL Server

Question

This will depend on the database but for SQL Server, this could be achieved as follows:

alter table Example
add NewColumn int identity(1,1)

It would help if you posted what SQL database you're using. For MySQL you probably want auto_increment:

ALTER TABLE tableName ADD id MEDIUMINT NOT NULL AUTO_INCREMENT KEY

Not sure if this applies the values retroactively though. If it doesn't you should just be able to iterate over your values with a stored procedure or in a simple program (as long as no one else is writing to the database) and set use the LAST_INSERT_ID() function to generate the id value.

for oracle you could do something like below

alter table mytable add (myfield integer);

update mytable set myfield = rownum;

And the Postgres equivalent (second line is mandatory only if you want "id" to be a key):

ALTER TABLE tableName ADD id SERIAL;
ALTER TABLE tableName ADD PRIMARY KEY (id);

Just using an ALTER TABLE should work. Add the column with the proper type and an IDENTITY flag and it should do the trick

Check out this MSDN article http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspx on the ALTER TABLE syntax

for UNIQUEIDENTIFIER datatype in sql server try this

Alter table table_name
add ID UNIQUEIDENTIFIER not null unique default(newid())

If you want to create primary key out of that column use this

ALTER TABLE table_name
ADD CONSTRAINT PK_name PRIMARY KEY (ID);