Sql: add a column to existing table and uniquely number them

Question

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 add 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?

Answer 1

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)

Answer 2

first add new column to existing table in your db, and after that you can write one store procedure to fill those fields in table, or you can do it from your code. Read some tutorial how to write store procedure, for db that you use.

Answer 3

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

Answer 4

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.

Answer 5

for oracle you could do something like

alter table mytable add (myfield integer);

update mytable set myfield = rownum;

Answer 6

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.