how to generate unique id number 1

[bookouri]

I'm used to working with Oracle and am just recently being exposed to sql server.

I'm currently cleaning up an existing table that doesnt have a good primary key. In oracle I would have used something like "update mytable set new_id=1000+rownum"
to load the table with a unique col to use as a primary key. Can anybody point in in the right direction to accomplish this with t-sql?

 

[PWise]

Update table
set new_id=newid()

 

[bookouri]

thanks, all the examples i found were using some kind of
select rank() over ( order by..... kind of construction.. to accomplish something similar..

 

[markros]

Try

;with cte as (select *, Row_Number() over (order by (select null)) as RowNum from myTable)

update cte set NewID = RowNum

After that you can make your NewID to be Identity primary key, just make sure to set seed to be the max of current NewID.

 

[bookouri]

wow, i dont understand that ";with cte as" construction but it works????

 

[markros]

CTE is Common Table Expression for short. Google on "Common Table Expressions"

You can also review this blog

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/cte-and-hierarchical-queries

It has links to several good other blogs on this topic.