statement to add row number

[denimblue]

I have been doing the below statement to populate a column called &quot;o_rowid&quot; with a number. The table &quot;AP_Dept1113&quot; has over 80,000 rows of data. After I do an import into this table this column is <NULL> where the new data is. It has to have a row number to accomplish what we do with our front end software. The statement runs for over three hours, but never finishes correctly. Does anyone know a better way to assign a number + 1 to this column?

declare @currrow char(19)
declare @counter int
declare @numrows int
declare @numrows2 char(19)

declare x cursor for select count(*) from AP_Dept1113
open x
fetch x into @numrows
close x

select @numrows2 = convert( char(19), @numrows )

declare stamper cursor for select o_rowid from AP_Dept1113 for update of o_rowid

select @counter = 1
open stamper

while (@numrows > 0)
begin

fetch stamper into @currrow

print @currrow

update AP_Dept1113 set o_rowid = convert ( char(10), @counter) where current of stamper

select @counter = @counter + 1

select @numrows - 1

end

 

[sonname]

It seems that you column &quot;o_rowid&quot; needs to be an identity column. This way it will increment by itself anytime you insert a row into that table. You wouldn't need to do your procedure to update that row.

 

[SQLSister]

Rule one, avoid cursors at all costs. Either set an identity column to give you a row number equivalent inorder of the rows inserted or if you want the rows as per particular query order (such as ordered by last name) then you create a temp table with and identity column int it and insert the results of the query into the temp teable. Then you do any further processing usingthe data fromthe temp table.