add an auto-increment field and populate to an existing table with data

How do I add an auto-increment field, or identity seed to an existing table?  Does it have to be a primary key?
amhinkelAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
grant300Connect With a Mentor Commented:
The ALTER TABLE command will do it for you.  It not only adds the column, but when defined with the IDENTITY qualifier also populates it.

Below is a complete example.  The line you want to pay attention to is "ALTER TABLE ADD <column_name> <datatype> IDENTITY"
Remember that you must use a numeric or decimal data type with for identity columns with no decimal points.  These are the only "exact" datatypes in Sybase.

==============

create table fubar (col1 int, col2 varchar(32))
go

insert into fubar values (1,'one')
insert into fubar values (2,'two')
insert into fubar values (3,'three')
insert into fubar values (4,'four')
insert into fubar values (5,'five')
insert into fubar values (6,'six')
commit
go

select * from fubar
go

alter table fubar add col0 numeric(12) identity
go

select * from fubar
go
0
 
Bob LearnedCommented:
Not really an "expert", but :

http://manuals.sybase.com/onlinebooks/group-as/asg1200e/whatsup/@Generic__BookTextView/859;pt=460

Identity columns are by there very nature primary keys.  Each value must be unique, but the value is unimportant.

Bob
0
 
Bob LearnedCommented:
Yeah, that's a better explanation than posting a help reference for the ALTER TABLE command  :)

Bob
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
amhinkelAuthor Commented:
Thank you both.  I meant to accept the one from grant300 as the Accepted Answer.  How do I fix this?
0
 
amhinkelAuthor Commented:
I requested to have the points transferred to grant300 through Customer Support.  Thanks again!
0
 
grant300Commented:
amhinkel,

You are very welcome, and thank you for going out of your way to fix the points.  I appreciate it.

Regards,
Bill
0
All Courses

From novice to tech pro — start learning today.