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

Eduardo Fuerte
Eduardo Fuerte used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
That would depend on the version of SQL Server that you're running.

On 2008 and older, no.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
I'm using 2014, could you point the command (or a reference to it) ?
Senior Software Developer
Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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 Analyst

Author

Commented:
Hi

Going the easyest way

img001
It's not possible to update the identity column with the old values...
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 Analyst

Author

Commented:
Even doing that

img002
I believe that you'll need to run both statements together.  Highlight them and run them in a single step.
Russ SuterSenior Software Developer
Commented:
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 Analyst

Author

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...
Also remember that this will affect any foreign keys that are in place.  You'll have to reestablish them.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Really a good solution the 2nd option you give.

Thanks for the guidance!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial