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

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

User · Answer

for oracle you could do something like below  alter table mytable add  myfield integer    update mytable set myfield   rownum

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server