Update table field to an incremental value

[Saturn57]

I have an existing table that I would like to add a field to that is incremental. Start at 1 and increment by 1. this is a simple solution I know but what is the query to update the field automatically so I don't have to type 1 to .....
Thanks In Advance

 

[RiverGuy]

Identity

 

[Saturn57]

Thanks. But is there a query that will change all the numbers in the field to be incremented by one after the fact.

 

[jbenson001]

An identity column is incredmented by the seed automatically on each insert. You do NOT need to Insert that value yourself.

 

[markros]

You would need to pick some field you want your increment order be based on.

SQL Server 2005 and up

;with cte (select myCurrentPK, row_number() over (order by SomeOtherField) row_number from myTable)

update myTable set myIDField = Row_Number from myTable inner join cte on myTable.myCurrentPK = cte.myCurrentPK