Hi, all,
I have a question about adding a unique key column to an existing table.
what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?
Thanks a lot in advance for any guidance.
Here is an example of how to add an identity column
create table TestID (SomeField varchar(49))
insert into TestID values ('1.1.9')
insert into TestID values ('2.2.2.10')
insert into TestID values ('2.2.2.8')
insert into TestID values ('2.2.2.9')
insert into TestID values ('1.1.7')
insert into TestID values ('1.1.8')
insert into TestID values ('1.2.1')
insert into TestID values ('1.1.1')
insert into TestID values ('1.1.10')
insert into TestID values ('1.1.10')
GO
select * from TestID
GO
alter table TestID add IdField int identity not null
Go
select * from TestID
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Hi, thanks a lot.
Got it done, but the problem is: the new added identity column is not in order? like its values from 1 to 82 is actually not in the order, it is sorted out based on other columns values in the existing table. So how can I modify its values from 1 to 83 in the oder?
Thanks a lot.
|||Order is never guaranteed for a table, use ORDER BY to get the rows back in the order that you want|||Hi, thanks a lot.