Press enter to see results or esc to cancel.

Add Identity Column To Existing Table in Sql Server

Have you ever wondered how to add an identity column to an existing table in SQL Server? Though you cannot simply give an existing ID column the identity property, what follows is a neat hack that allows you add an identity ID column to an existing table without losing data.

What you first need to do is add a new column with identity property to your existing table. Say your table is called [Table1] and it has an existing column [ID]

ALTER TABLE [Table1]
ADD COLUMN [ID_new] INT IDENTITY(1, 1);

After that, you need to drop your existing [ID] column

ALTER TABLE [Table1] DROP COLUMN [ID]

And finally, you rename your newly added [ID_new] column back to your original [ID] name

EXEC sp_rename '[Table1].[ID_new]', '[ID]', 'Column';

Voila, your table now has an ID column with IDENTITY property set.

Comments

Leave a Comment