[dba-SQLServer]Unworking Cursor

CREATE PROCEDURE gps_american_site_no AS
 create table #tmp_site (site_no int)
insert #tmp_site(site_no)
select max(site_no)
from site
Does anyone know anything about cursors?  I have a table that I have
imported from Access to SQL 7.  Table is named tblAmerican.  The unique ID
is named ID, the field I want to populated is named siteno.

I get the maximum value of the site_no from my site table (another SQL
table).  That works.  Then I think I am telling Herman (that is my computer)
to update the values of the site_no field in tblAmerican by one as it loopdy
doops through my first attempt at a cursor.  The only thing I get is the
first number in tblAmerican.  Says it is doing something one by one, but
only row one gets updated.

Why am I lost?


declare @tmp_site_no int, @tmp_id_no int

declare auto_site_cursor cursor for
select  id
from tblamerican

open auto_site_cursor

fetch next from auto_site_cursor into @tmp_id_no

while @@fetch_status = 0
begin

   update tblamerican
   set siteno =  #tmp_site.site_no + 1
   from  #tmp_site,
        tblamerican
   where  tblamerican.id=@tmp_id_no
        
   update  #tmp_site
   set site_no = site_no + 1

  
   fetch next from auto_site_cursor into @tmp_site_no
                                                              

end

close auto_site_cursor

deallocate auto_site_cursor

drop table #tmp_site