How to Reset Identity Column Value In SQL Server without Delete Records.

Some considerations:
- Use a table variable instead of #tempTable. Temporary tables create an actual table in the temp database, that needs to be dropped afterwards. When something bad happens, the table will not be dropped.
- Use a FAST_FORWARD cursor, when it is read-only. For large tables, it performs a lot better.
- Try not to use a cursor. Cursors are the last option to consider.
- Try not to update table design. Resetting identity columns and foreign key constraints are easely forgotten after code like this. Have a look at the query SQL management studio performs when doing a table design change(it creates another table, and uses a transaction).

A better performing query (without my last consideration in mind) would be:

USE YourDataBase

DECLARE @TmpTable TABLE (id int, rowNumber int)

INSERT INTO @TmpTable
SELECT Id,ROW_NUMBER() OVER (ORDER BY Id ) AS RowNumber FROM MyTable

UPDATE MyTable SET ID = tmp.rowNumber
FROM MyTable t
INNER JOIN @TmpTable tmp ON tmp.id = t.ID

Or without variables:

WITH cte_Temp(Id, RowNumber)
AS
(
    SELECT Id,ROW_NUMBER() OVER (ORDER BY Id ) AS RowNumber FROM MyTable
)
UPDATE MyTable SET ID = tmp.rowNumber
FROM MyTable t
INNER JOIN cte_Temp tmp ON tmp.id = t.ID