Add Auto Incremental to existing column

Question

Add Auto Incremental to existing column

rtrice81

SSC Rookie

Points: 48
More actions
March 15, 2018 at 7:27 am

#333942

Somehow when adding a table from another database there was no PK or Auto Incremental column set, now I would like to create a PK and set this column to auto incremental.
I was able to get the PK set to the column, however, I can't seem to get auto incremental to work, here is the query I am using to alter the column
ALTER TABLE dbo.MyTable ALTER COLUMN MyColumn INT IDENTITY(1,1)
Any help would be great, based on my research it appears that I may have to drop the table and recreate it, however, I don't want to lose the structure of the table and I don't want to lose any of the rows now.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

rtrice81 - Thursday, March 15, 2018 7:27 AM
Somehow when adding a table from another database there was no PK or Auto Incremental column set, now I would like to create a PK and set this column to auto incremental.
I was able to get the PK set to the column, however, I can't seem to get auto incremental to work, here is the query I am using to alter the column
ALTER TABLE dbo.MyTable ALTER COLUMN MyColumn INT IDENTITY(1,1)
Any help would be great, based on my research it appears that I may have to drop the table and recreate it, however, I don't want to lose the structure of the table and I don't want to lose any of the rows now.

Do you need to maintain the existing numbering? If not, just add a new INT IDENTITY(1,1) NOT NULL column to your table and then set it as the PK.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

rtrice81 SSC Rookie Points: 48 More actions · Answer 2

Can I delete/drop the current column and add the new one without losing any data?

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

rtrice81 - Thursday, March 15, 2018 8:05 AM
Can I delete/drop the current column and add the new one without losing any data?

Sure. All you would lose would be the data in the 'current' column.
As always, please run this on a development version of your table first, to check that all is well, before running on anything in Production.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.