Changing a Column to Identity after creation.


Today I had to change the Primary key columns in tables so that they can become identity columns but I couldn’t do it using Alter Table x Alter column y IDENTITY(1,1) command. So I found out that you can’t use ALTER TABLE to change a column into IDENTITY column (except on SQL Server CE!). One way is to rename the table, create a new and move over the data. You need to have SET IDENTITY_INSERT
on for the table when you move the data.

You can also do it in Enterprise Manager – which will rename and move data behind the scenes.

Thanks to this thread

Advertisements

Author: Imran Akram

A .NET/SharePoint Consultant and a political enthusiast who believes in putting the feet of the elected and/or unelected officials to fire in order to get good governance.

One thought on “Changing a Column to Identity after creation.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s