Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding a Sequentially Numbered Column

Author  Topic 

UF-Supra
Starting Member

11 Posts

Posted - 2006-08-04 : 09:53:21
Hey guys,

Was wondering if there was a way to add a new column to a table, and then use some type of INSERT statement to sequentially number the column from 1-end of the table without using an IDENTITY column.

For instance if I wanted to add a sequentially numbered column in a table with 50 rows that already had an IDENTITY column.

Thanks in advance.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-04 : 10:06:26
if the sequence of the data of New_Column is same as that of the identity Col:

Just add the New_Col & run the following

Update Urtbl set New_Col = IdentityCol



Srinika
Go to Top of Page

UF-Supra
Starting Member

11 Posts

Posted - 2006-08-04 : 10:21:44
The actual data in the Fields of the identity column will not be the same.
Example: The IDENTITY column starts from 67 and I want the new column to start from 1.
Go to Top of Page

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-04 : 10:48:49
Is there any pattern or relationship between identity col & the new field ?
or else some other way that u want the new field to be filled with data starting from 1?

try the following
declare @i int
Set @i = 1

update urTbl set @i = @i + 1, New_Col = @i


Srinika
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-04 : 11:22:26
Where do you want to show data?
If you use front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

UF-Supra
Starting Member

11 Posts

Posted - 2006-08-04 : 14:44:13
quote:
Originally posted by Srinika

Is there any pattern or relationship between identity col & the new field ?
or else some other way that u want the new field to be filled with data starting from 1?

try the following
declare @i int
Set @i = 1

update urTbl set @i = @i + 1, New_Col = @i


Srinika




Thanks man that worked Beautifully!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-04 : 18:11:08
You might want to read this...it's all about the transaction

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page
   

- Advertisement -