I was helping a client change to a new web host. During this process I discovered an Access database was powering part of their web site. This had to go!

I imported the database into SQL Server. Unfortunately, this process removed the identify specifications on the primary key column for one of the tables. This means that the primary key column would not automatically fill in with an automatically incrementing integer. I opened up the table design, and tried to change the field back to an ID. Unfortunately, SQL Server does not allow you to add an identity specification to an existing column on an existing table.

How do I fix that?

What is the Process?

The process was tricky to figure out, but not all that too hard. Basically, do this:

  1. Create a new table with all the same columns as the old table. Make sure this new table has an identity column.
  2. Enable Identity Insert on the new table.
  3. Copy all data from the first table into the new table.
  4. Disable Identity Insert on the new table.
  5. Delete the original table.
  6. Rename the new table to the original table's name.

Then you're done.

What is the Code?

This whole process can be done quickly with a SQL Server script.

First, create the new table:

view plain print about
1CREATE TABLE dbo.newtable_temp
2 (
3 Id int NOT NULL IDENTITY(1, 1),
4 textField nvarchar(100) NULL,
5 bitField bit not NULL
6 /* other fields */
7 )
8ON [PRIMARY]
9go

Then enable the identity insert:

view plain print about
1SET IDENTITY_INSERT dbo. newtable_temp ON
2Go

Next, use this script to insert all the rows from the original table into the new table:

view plain print about
1IF EXISTS ( SELECT *
2 FROM dbo.newtable )
3 INSERT INTO dbo. newtable _temp ( Id, textField, bitField /* other fields */)
4 SELECT Id, textField, bitField /* other fields */
5 FROM dbo. newtable TABLOCKX
6go

Then turn off the identity insert:

view plain print about
1SET IDENTITY_INSERT dbo.newtable_temp ON
2Go

Delete the original table:

view plain print about
1DROP TABLE dbo.newtable
2Go

Finally rename the old table:

And you should be good to go!

Much thanks to this Stack Overflow question for helping me out.