March 15, 2018 at 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.
March 15, 2018 at 7:32 am
rtrice81 - Thursday, March 15, 2018 7:27 AMSomehow 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.
March 15, 2018 at 8:05 am
Can I delete/drop the current column and add the new one without losing any data?
March 15, 2018 at 8:17 am
rtrice81 - Thursday, March 15, 2018 8:05 AMCan 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.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply