SQL Server convert IDENTITY column to non-IDENTITY

I made a fundamental mistake in the database architecture, and learnt that identity columns are great for programmatically generated database rows, however, for prescriptive data (such as a list of categories that does not change via the UI) then the id column is best managed manually to ensure consistency between environments.

I ended up just scripting creation of a temp table, inserting the data, deleting the old table, and renaming the temp table. I am leaving this as a reminder of the pain that a small mistake in database architecture can make once in production!

The resolution was based on this Stack Overflow answer but with a few differences especially around constraints.

1. Create the new desired column
   alter table Categories add newid int;

2. Copy contents from the old to the new
   update Categories set newid=id;

3. Find any FK constraints on the old column and drop them

4. If your old column was also a primary key, there will be a PK constraint you need to remove. See instructions from Microsoft.

5. Remove the old column
   alter table Categories drop column id;

6. Rename the new column back to the same name as the old
   EXEC sp_RENAME 'Categories.newid' , 'id', 'COLUMN';

7. Re-establish any FK constraints that you previously removed

8. Learn from mistakes