Updating the rows with Identity values

Question

Post reply

Updating the rows with Identity values

virgo

Ten Centuries

Points: 1266
More actions
April 26, 2008 at 12:45 am

#181733

Can any one plz let me know how can i generate a identity values on existing table
for emaple I have table Test with field name Name1 with values.
Name1
Vin
Jim
Sam
Jan
now i want to add a new column called iD and generate the numbers from 1 to total number of records in table like
Name1 ID
Vin 1
Jim 2
Sam 3
Jan 4
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Just alter the table to add the ID column making sure that it has the IDENTITY property included.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

virgo Ten Centuries Points: 1266 More actions · Answer 2

thanks jiff....how can we do the same with out using identity or cursors i.e by just using while loop and update.....i missed telling this in my previous post

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Why bother? If you want to update a table with an IDENTITY column, then just do it.

If you're trying to create a query that returns a numbered list, then you need to tell me which version of SQL you're using.

And, I know I can get things done in a "jiffy", but the name is "Jeff", not "jiff"... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

virgo Ten Centuries Points: 1266 More actions · Answer 4

:w00t: ok Jeff....just in some cases i need to do that way....am using sql server 2005....

thanks much for ur reply

Ian (SSC) SSC-Addicted Points: 407 More actions · Answer 5

If you just want a numbered list from a table you could use the ROW_NUMBER() function:

SELECT col1, ROW_NUMBER() OVER (ORDER BY col1)

FROM tablea

I've not used it much myself but it may be of use to you.

Ian

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

virgo (4/28/2008)
:w00t: ok Jeff....just in some cases i need to do that way....am using sql server 2005....
thanks much for ur reply

Ian's method using ROW_NUMBER() OVER will work just fine, then.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)