SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding a Sequentially Numbered Column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

UF-Supra
Starting Member

11 Posts

Posted - 08/04/2006 :  09:53:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 08/04/2006 :  10:06:26  Show Profile  Reply with Quote
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 - 08/04/2006 :  10:21:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 08/04/2006 :  10:48:49  Show Profile  Reply with Quote
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

India
22742 Posts

Posted - 08/04/2006 :  11:22:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 08/04/2006 :  14:44:13  Show Profile  Reply with Quote
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 - 08/04/2006 :  18:11:08  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000