Is it possible to make a SQLServer table's PK column to be indentity after the table is populated?

Hi Experts!

Is it possible to transform an existing PK column to be an indentity column too, after the table is populated?

How could I do that?

Thanks in advance
Eduardo FuerteDeveloper and AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
That would depend on the version of SQL Server that you're running.

On 2008 and older, no.
Eduardo FuerteDeveloper and AnalystAuthor Commented:
I'm using 2014, could you point the command (or a reference to it) ?
Russ SuterSenior Software DeveloperCommented:
Yes, sort of...

There are 2 options.
1. Create a new table with the identity column you want, insert into new table from old table, drop old table.
CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Open in new window

2. Create a new column in the existing table with identity and drop the old column.
Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

Open in new window

The 2nd way is probably easier. You can also create an UPDATE statement to populate the new identity column with existing values as long as your original column doesn't already violate PK constraints.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Kent OlsenDBACommented:
As far as I know, there is no magic one-line command.  But you can still do it.  If there are no foreign keys that reference that column, the easiest is to

ALTER TABLE {mytable} ADD {newcolumn} INT IDENTITY(1,1) NOT NULL;

Copy the values in the PK to the new column.
Drop the old PK column.
Rename the new column to the same name as the dropped column.

If there are foreign keys against the table, it gets a lot more involved.
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Hi

Going the easyest way

img001
It's not possible to update the identity column with the old values...
Kent OlsenDBACommented:
Set IDENTITY_INSERT ON

and you should be able to update the row to have the values in the original row.

Remember to set IDENTITY_INSERT OFF again when you're done!  :)
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Even doing that

img002
Kent OlsenDBACommented:
I believe that you'll need to run both statements together.  Highlight them and run them in a single step.
Russ SuterSenior Software DeveloperCommented:
Ah, you're right, IDENTITY_INSERT works for INSERT but not UPDATE. Your workaround would be to copy the current table into a temp table, delete all rows in the current table, then insert temp values back into the table with IDENTITY_INSERT on. If the table is HUGE that could be an issue. Perhaps option 1 is the better choice in this case.
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Since I'm doing a migration data that must preserve the table codes, I have to go this way... just a little more time...
Kent OlsenDBACommented:
Also remember that this will affect any foreign keys that are in place.  You'll have to reestablish them.
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Really a good solution the 2nd option you give.

Thanks for the guidance!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.