Remove foreign key reference (if any) from
YourTabe and set Is Identity to
No in column properties windows in Microsoft SQL Server Management Studio. Execute the bellow SQL using Management Studio.
USE YourDataBase
SELECT [YourTableId],ROW_NUMBER() OVER (ORDER BY YourTableId ) AS RowNumber
into #tempTable from [YourTable]
DECLARE your_table_cursor CURSOR FOR
SELECT [YourTableId], RowNumber
FROM #tempTable
OPEN your_table_cursor
DECLARE @YourTableId int
DECLARE @RowNumber int
FETCH NEXT FROM your_table_cursor
INTO @YourTableId, @RowNumber
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE [YourTable]
SET [YourTableId] = @RowNumber
WHERE [YourTableId] = @YourTableId
FETCH NEXT FROM your_table_cursor
INTO @YourTableId, @RowNumber
END
CLOSE your_table_cursor
DEALLOCATE your_table_cursor
DROP TABLE #tempTable
Set Is Identity to
Yes and set Identity increment to 1 and Identity Seed to 1 in column properties windows in Management Studio. Then set reference.