Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2008-10-20
8
1,107 Views
Last Modified: 2011-10-19
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
Comment
Question by:mechanism
  • 4
  • 4
8 Comments
 

Author Comment

by:mechanism
ID: 22756027
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
 
LVL 17

Expert Comment

by:HuyBD
ID: 22756042
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
 
LVL 17

Expert Comment

by:HuyBD
ID: 22756073
if not, try remove ID column and run query
ALTER TABLE TABLE_01
ADD ID int identity

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:mechanism
ID: 22756125
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
 

Author Comment

by:mechanism
ID: 22756130
Names of fields are not important, I just want to add auto increment in column 'ID'
0
 
LVL 17

Accepted Solution

by:
HuyBD earned 125 total points
ID: 22756195
you can change to this
ALTER TABLE TABLE_01
ADD NEWID int identity

Open in new window

0
 
LVL 17

Expert Comment

by:HuyBD
ID: 22756196
then update
update TABLE_01 set ID=NEWID

Open in new window

0
 

Author Closing Comment

by:mechanism
ID: 31507755
Thank you for your solution, was exactly what I was looking for.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question