Adding an Identity to an Existing Column

Adding an identity to an existing column

You can't alter the existing columns for identity.

You have 2 options,

1. Create a new table with identity & drop the existing table

2. Create a new column with identity & drop the existing column

Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you will lose all data if 'if not exists' is not satisfied, so make sure you put the condition on the drop as well!

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'

Approach 2 (New column) You can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

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'

See the following Microsoft SQL Server Forum post for more details:

How to alter column to identity(1,1)

Convert an existing Column to Identity

As you are using SQL Server 2012, another possible alternative could be to create a sequence object that has a starting value of the highest ID +1 already in your table, then create a default constraint for your column using GET NEXT VALUE FOR and reference your sequence object you just created.

How do I add the identity property to an existing column in SQL Server

I don't beleive you can do that. Your best bet is to create a new identity column and copy the data over using an identity insert command (if you indeed want to keep the old values).

Here is a decent article describing the process in detail:
http://www.mssqltips.com/tip.asp?tip=1397

How to add Identity Column in SQL server?

You can only have 1 Identity Column per Table. So if you don't have one already, Just alter the table and add the Column. Like this

ALTER TABLE YourTableName
ADD IdCol INT IDENTITY(1,1)

Is it safe to add IDENTITY PK Column to existing SQL SERVER table?

Give this a go; it's rummaged up from a script we used a few years ago in a similar situation, can't remember what version of SQLS it was used against.. If it works out for your scenario you can adapt it to your tables..

SELECT MAX(Id)+1 FROM causeCodes -- run and use value below

CREATE TABLE [dbo].[CauseCodesW]( [ID] [int] NOT NULL IDENTITY(put_maxplusone_here,1), [Code] [varchar](50) NOT NULL, [Description] [varchar](500) NULL, [IsActive] [bit] NOT NULL )


ALTER TABLE CauseCodes SWITCH TO CauseCodesW;

DROP TABLE CauseCodes;

EXEC sp_rename 'CauseCodesW','CauseCodes';

ALTER TABLE CauseCodes ADD CONSTRAINT PK_CauseCodes_Id PRIMARY KEY CLUSTERED (Id);

SELECT * FROM CauseCodes;

You can now find any tables that have FKs to this table and recreate those relationships..

Start identity in an existing table / column from a particular number

I found the solution:

1. At SSMS -> Tools -> Options -> Designers: Remove the V from 'Prevent Saving changes that require table re-creation'.
2. Open the table in Design Mode, and add the V to the Identity Spcification property.

This will also reseed it automatically.

How can I change existing column as Identity in PostgreSQL 11.1

Following the documentation

ALTER TABLE patient 
    ALTER patientid SET NOT NULL,  -- optional
    ALTER patientid ADD GENERATED ALWAYS AS IDENTITY 
        (START WITH 2);  -- optional

Add NOT NULL constraint if the column does not have the constraint yet. The optional clause START WITH start changes the recorded start value of the sequence.

Test it in DB<>Fiddle.

---

Related Topics