Give existing rows a uniqueid

• sc-w

  Hall of Fame

  Points: 3667

  More actions

  April 16, 2009 at 3:28 am

  #130584

  Hi,

  I have 8000 rows in a table and i need to give them all a unique id, the logical thing is to give them a number from 1 to 8000 the problem is there are lot's of duplicate records and nothing unqiue to match upon to use the following query, i have tried looking for hours to get something unique but there isn't anything. Unfortunatly i did not build the table so have no option but to work with what i have.

  Is there anything simple i can do in SQL that i'm missing?

  Thanks for any help

  <%
Server.ScriptTimeout = 6000

  %>

  <% i=0 mySQL="select person_code from wce_ilr"
Set oRs = connStr.Execute(mySQL)
i=0

  do while not oRs.eof
i=i+1

  mySQL2="UPdate wce_ilr set uniqueid="&i&" WHERE person_code='"&ors("person_code")&"'"
'response.write(mySQL2)
'response.end
Set oRs1 = connStr.Execute(mySQL2)

  ors.movenext
loop

  %>

• Kruti Kansara

  Hall of Fame

  Points: 3118

  More actions

  April 16, 2009 at 3:46 am

  #978682

  Hi,

  You can do some like this (if you want your unique key based on your any other column value)

  create table #temp (uniqueid varchar(10), uname varchar(5), age int)

  insert into #temp (uname, age)

  (

  select 'aaaa', 30 union all

  select 'bbbb', 25 union all

  select 'cccc', 35 union all

  select 'dddd', 40 union all

  select 'ffff', 10

  )

  alter table #temp add uid int Identity(1,1)

  select * from #temp

  update #temp set uniqueid=uname+convert(varchar(5),uid,0)

  select * from #temp

  drop table #temp

  Also, please do not provide VBScript code here. Try to provide SQL only.

  "Don't limit your challenges, challenge your limits"

• sc-w

  Hall of Fame

  Points: 3667

  More actions

  April 16, 2009 at 12:58 pm

  #979135

  Hey, thanks for that i tweaked ti and got the result i needed. Thanks again