How to Alter a Table for Identity Specification Is Identity SQL Server

Alter column to be identity

You could try this approach:

1. Insert rows with old ID with SET IDENTITY_INSERT <new table> ON. This allows you to insert your own ID.
2. Reseed the Identity, setting it to the highest ID value +1 with DBCC CHECKIDENT ('<new table>', RESEED, <max ID + 1>). This will allow your Identity to increase from the highest ID and forward.

Something like this in code:

-- Disable auto increment
SET IDENTITY_INSERT <new table> ON
-- <INSERT STUFF HERE>
SET IDENTITY_INSERT <new table> OFF

-- Reseed Identity from max ID
DECLARE @maxval Int
SET @maxval = ISNULL(
(
    SELECT
        MAX(<identity column>) + 1
    FROM <new table>
), 0)
DBCC CHECKIDENT ('<new table>', RESEED, @maxval)

EDIT: This approach requires your ID-column to be an Identity, of course.

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)

Is there any query to set identity specification to false or true

Adding New Column First -

alter table tablename add columnname int

Then copying the data from identity to column to new column added using above query -

update tablename set columnname = identitycolumnname

Now Dropping identity column -

alter table tablename drop column identitycolumnname

Then finally renaming a new column inserted to a identity Column name -

EXEC sp_RENAME 'tablename.columnname' , 'identitycolumnname', 'COLUMN'

How to update Identity Column in SQL Server?

You can not update identity column.

SQL Server does not allow to update the identity column unlike what you can do with other columns with an update statement.

Although there are some alternatives to achieve a similar kind of requirement.

• When Identity column value needs to be updated for new records

Use DBCC CHECKIDENT which checks the current identity value for the table and if it's needed, changes the identity value.

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

• When Identity column value needs to be updated for existing records

Use IDENTITY_INSERT which allows explicit values to be inserted into the identity column of a table.

SET IDENTITY_INSERT YourTable {ON|OFF}

Example:

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in the identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
-- Delete the old row of which you have inserted a copy (above) (make sure about FK's)
DELETE FROM YourTable WHERE ID=3
GO
--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF

Modify the identity specification of primary key in database

try this .
go to sql server management studio then :
tools -> options -> designers -> Prevent saving changes that require table re-creation (uncheck this )

after this try again to set identity column

Updating the id column's Identity Specification on an active table

You can not alter an existing column and add identity(), but you could drop the existing column and add a new column with identity()., and it will automatically populate.

alter table t drop column some_id;
alter table t add some_id int not null identity(1,1);

How to set identity column to created table in SQL server

ALTER TABLE [UserName] DROP COLUMN [ID];

ALTER TABLE [UserName] 
    ADD [ID] integer identity not null;

Alter existing Column Identity From No To yes SQL Server

Create the new ID column, copy data from the old column, delete the old column:

EXEC sp_RENAME 'MyTable.TermID' , 'TermID_OLD', 'COLUMN'

ALTER TABLE MyTable
  ADD TermID INT IDENTITY(1,1) NOT NULL

SET IDENTITY_INSERT MyTable ON

UPDATE MyTable
SET TermID = TermID_OLD

SET IDENTITY_INSERT MyTable OFF

ALTER TABLE MyTable DROP COLUMN TermID_OLD

---

Related Topics