user_384
0
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?
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 KEYNot 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;
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
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.