filling column with indices

[clyde11]

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

 

[RAS56]

Here is a stab at what you seem to be asking for. Not sure from your post if you want the data in your table to be in col2 order but made that assumption. I'm sure there are probably more elegant ways to do this too.

-- create sample table
create table testtbl (col1 int, col2 char(1), col3 char(2))
insert into testtbl values (0,'a','b')
insert into testtbl values (0,'c','d')
insert into testtbl values (0,'e','f')

-- create a temp table to use for creation of the id value
create table #tbl (id int identity, col2 char(1))

-- populate the temp table with id values based on col2 order
insert into #tbl select col2 from testtbl order by col2

-- update sample table with id numbers
update a set a.col1=b.id from testtbl a, #tbl b where a.col2=b.col2

-- display results
select * from testtbl

-- clean up stuff
drop table #tbl
drop table testtbl

 

[clyde11]

Hi, thanks !
what i ment (and understood it now) is that i was simply needed to use :

ALTER TABLE [tbl]
DROP COLUMN [col1]

ALTER TABLE [tbl]
ADD [col1] bigint identity(1,1)

although its fine, i don't really know how to do this
without ALTER (without deleting and adding [col1])