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

I want to add a column to an existing legacy database and write a procedure by which I can assign each record a different value. Something like adding a column and autogenerate the data for it.

Like, if I add a new column called "ID" (number) I want to then initialize a unique value to each of the records. So, my ID column will have records from say 1 to 1000.
How do I do that?

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.

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);

for oracle you could do something like below

alter table mytable add (myfield integer);

update mytable set myfield = rownum;

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

If you don't want your new column to be of type IDENTITY (auto-increment), or you want to be specific about the order in which your rows are numbered, you can add a column of type INT NULL and then populate it like this. In my example, the new column is called MyNewColumn and the existing primary key column for the table is called MyPrimaryKey.

UPDATE MyTable
SET MyTable.MyNewColumn = AutoTable.AutoNum
FROM
(
    SELECT MyPrimaryKey, 
    ROW_NUMBER() OVER (ORDER BY SomeColumn, SomeOtherColumn) AS AutoNum
    FROM MyTable 
) AutoTable
WHERE MyTable.MyPrimaryKey = AutoTable.MyPrimaryKey  

This works in SQL Sever 2005 and later, i.e. versions that support ROW_NUMBER()

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);

Depends on the database as each database has a different way to add sequence numbers. I would alter the table to add the column then write a db script in groovy/python/etc to read in the data and update the id with a sequence. Once the data has been set, I would add a sequence to the table that starts after the top number. Once the data has been set, set the primary keys correctly.