Microsoft SQL Server Azure

Updating a pre-existing Microsoft SQL table to have an identity column without losing data

You’ve created a Microsoft SQL Server table, added some data and then realised that integer column you created would be much better off as an identity column. You go to alter the table but SQL Server won’t allow you to change your integer column into an identity one without re-creating the table and losing the data… How to proceed?

The following is my regular workaround to the problem.

Introducing our example table

Let’s assume we have a table of the following structure:

We’ve performed our data analysis and now want to formalize this into a production table. To do this we need to create a primary key and we’ve flagged OrderID as a column that we want to convert into an identity type.

Firstly, clone the table

Before we do anything we’re first going to need a copy of the table, including the source data. To do this we use select into and move everything into a temporary table:

Next – drop and re-create our table

Now that we have a backup of our table and data we need to re-create our original table with an identity column.

Finally – Enable identity_insert and re-populate our data

We now need to re-insert our data from the  temporary table into the production one. Given that we now have an identity column we must first enable identity_insert so we can populate our own CustomerId’s.

And that’s it! We now have an updated table with an identity column and our original data.