[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3038
  • Last Modified:

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?
0
amhinkel
Asked:
amhinkel
  • 2
  • 2
  • 2
1 Solution
 
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
 
grant300Commented:
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:
Yeah, that's a better explanation than posting a help reference for the ALTER TABLE command  :)

Bob
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now