• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1153
  • Last Modified:

Adding a serial number column in a table (SQL Server 2000)

I have a table in SQL Server 2000, named TABLE_01.
I have a field named ID.
I would like to update it with a serial number starting from 1 (1, 2, 3, 4, ... etc) for all records.
How shoud I do that?

ROW_NUMBER() function does not work.
0
N M
Asked:
N M
  • 4
  • 4
1 Solution
 
N MConsultantAuthor Commented:
Example:

I have TABLE_01
   ID        FIELD1     FIELD2
---------------------------------
<NULL>   ABC         DEF
<NULL>   SJH          KLH
etc..

and I want ..

   ID        FIELD1     FIELD2 etc..
---------------------------------
     1        ABC         DEF
     2        SJH          KLH
etc..
0
 
HuyBDCommented:
if ID is auto-increment you can query to add number column
select TABLE_01.*,
(slect count(*)+1 from TABLE_01 as t where t.ID<TABLE_01.ID) as num
from TABLE_01
order by ID

Open in new window

0
 
HuyBDCommented:
if not, try remove ID column and run query
ALTER TABLE TABLE_01
ADD ID int identity

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
N MConsultantAuthor Commented:
It doesn't seem to work although I did understand the logic behin it..
It actually brings a new column (or, as you correctly put it, the ID column if I delete it beforehand)
but with '1' only..

2008-10-20-114117.jpg
0
 
N MConsultantAuthor Commented:
Names of fields are not important, I just want to add auto increment in column 'ID'
0
 
HuyBDCommented:
you can change to this
ALTER TABLE TABLE_01
ADD NEWID int identity

Open in new window

0
 
HuyBDCommented:
then update
update TABLE_01 set ID=NEWID

Open in new window

0
 
N MConsultantAuthor Commented:
Thank you for your solution, was exactly what I was looking for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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