indexing a table.. - SQL Server Forums

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

indexing a table..

Author

Topic

pitt1
Starting Member

16 Posts

Posted - 2006-04-18 : 05:48:57

Hi all,
I have a column which i want to put inside ascending indices, that is, empty column which i want to enter
1,2,3,4.....[tble no. of rows]

so this table:

col1 | col2 | col3
-------------------
| a | b
| c | d
| e | f

becomes:

col1 | col2 | col3
-------------------
1 | a | b
2 | c | d
3 | e | f

thnks,
Ahron

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 06:06:07

Cant you make use of indentity property of a column?
Also if you want to number the results you can do it in front end application

Madhivanan

Failing to plan is Planning to fail

Norwich
Posting Yak Master

158 Posts

Posted - 2006-04-18 : 07:28:09

Try the following code to add the automated number for your rows:

Alter Table TableX
Add Col_ID Int Identity

Regards
N

The revolution won't be televised!

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-18 : 07:45:03

Somthing like this ..


Declare @tbl Table 
(
Col1 int,
Col2 char(1),
Col3 char(1)
)

Insert into @tbl(Col2,Col3)
Select 'a','b' union all
Select 'c','d' union all
Select 'e','f' 


Update @tbl  set col1 = b.rownum From 
(Select (Select Count(col2) From @tbl i Where i.col2 <= j.col2) As 'RowNum',* From @tbl j) AS b Inner Join 
@tbl c On b.Col2 = c.Col2

Select * From @tbl

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 08:51:26

That will be inefficient to the table that has millions of records

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-18 : 09:28:41

and also to the table which doesnt have a primary key..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources