Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1145
  • 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
mechanism
Asked:
mechanism
  • 4
  • 4
1 Solution
 
mechanismAuthor 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mechanismAuthor 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
 
mechanismAuthor 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
 
mechanismAuthor Commented:
Thank you for your solution, was exactly what I was looking for.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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