I wrote this script to do exactly what you are asking on a project i inherited. Hope this helps...k

--Drop temporary tables (if they exists) If you run this more than once.

Use [myDB]

Drop table tmpReIdTable

Go

--Create the temporary table

Use [myDB]

CREATE TABLE tmpReIdTable(

[newID] [int] IDENTITY(1,1) NOT NULL,

oldID [int] NULL,

CONSTRAINT [newID] PRIMARY KEY CLUSTERED

([newID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

--Insert into temp table from sourceTable using existing IDs

Use [myDB]

Insert into tmpReIdTable

(oldID)

select ID from sourceTable Order by WhateverYouWant

--Update sourceTable table using newID from temp table

Use [myDB]

Update sourceTable

set sourceTable.ID=tmpReIdTable.newID

from sourceTable inner join tmpReIdTable

on sourceTable.ID=tmpReIdTable.oldID

GO