CODEWITHSUNDEEP
sqlsql-servert-sql
timh
timh

Reputation: 371

Alter an existing Identity Column's Increment value

I am stumped,

I am trying to alter the increment value of Identity columns in a collection of existing MS SQL tables (which all have data) and have been trying to research if it is possible to do without writing custom scripts per table.
I can't find a solution that doesn't require dropping and recreating the tables which would require a different script for each table as they each have different column lists.

for example i want to change the existing table 

CREATE TABLE [dbo].[ActionType](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Action] [varchar](100) NOT NULL,
 CONSTRAINT [PK_ActionType] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

To

CREATE TABLE [dbo].[ActionType](
    [ID] [int] IDENTITY(1,5) NOT NULL,
    [Action] [varchar](100) NOT NULL,
 CONSTRAINT [PK_ActionType] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Via something like 

exec sp_AlterIncrement @TABLE_NAME = 'ActionType', @NEW_ICREMENT = 5

While keeping the data.

This would fix a big deployment issue i am facing right now so any help would be appreciated

Upvotes: 4

Views: 8855

Answers (6)

Ramon Baiges Miro
Ramon Baiges Miro

Reputation: 21

If you need to reenumerate or compress your Identity field, the easiest way is as follows:

Convert, temporarily, your identity filed into an integer Replace the values using for example an Excel sheet in other to fill them up Copy and Paste the column in your Excel file into the Int field. Save the table Open it again in design mode and change back the Int field into an Identity

If this Identity field is used in a child table, make sure you have a trigger to also export the new values into the dependant tables .

And that's all.

If you need to control Identity data in your applicaton, just change it to Int and manage the incremental values with code with the Dmax function.

Hope it helps

Upvotes: 0

Sylvain Bouche
Sylvain Bouche

Reputation: 1

Sometimes this is necessary.this might provide an answer. For example existing table is identity(1,1) [ex below would be A]

It contains value but you would like to change it to increment of to let's say so that it works well with another table [ex below would be B]

So a would have odd ids + whatever it use to contains.while be would now have even number

this script show you how to do it.

create table A(id int identity(1,1),v char)

insert into A
Select 'A'
union select 'B'
union select 'C'

go

create table B(id int identity(1,2),v char)

go

SET IDENTITY_INSERT B ON

GO 

insert into B(Id,v)
Select Id,v from A

go

SET IDENTITY_INSERT B OFF
GO 

insert into B
Select 'D'
union select 'E'

go

drop table A

go

EXEC sp_RENAME 'B' , 'A'

go

Select * from A

go

Select max(Id)+1 from A

go

create table B(id int identity(8,2),v char)

go

insert into B
Select 'A'
union select 'B'
union select 'C'

go


Select * from B

Upvotes: 0

Steve
Steve

Reputation: 5545

Couple of things, maybe too much info but helpful when do stuff like this. The following will set the increment to whatever you want:

DBCC CHECKIDENT ([DB.Schema.Table], reseed, 0) --First record will have a 1. You can set it to any value

If you want to insert data into a table that has an identity but you need to force the value to something specific, do this:

SET IDENTITY_INSERT [DB].[schema].[Table] ON

...Add your data here

SET IDENTITY_INSERT [DB].[schema].[Table] OFF

Upvotes: 0

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

You can not alter identity increment after you create it.It is possible just to change seed value with DBCC Chekident . You should drop and recreate the column.

Upvotes: 3

user2884957
user2884957

Reputation: 1

But anyways , I do not understand why you want to alter the identity value, Because anyhow you will keep the same as primary key or part of the clustered key.

Also, if any change in the column type is being required then i don't think that there is a possibility without altering the table structure.

Alter table ActionType Alter column ID

You can also revert to the original structure when not required. This can be used for the specified case as well, As if you require this on demand basis.

Please suggest so that i can provide the further feedback.

Upvotes: 0

iouri
iouri

Reputation: 2929

I had to do that before on a small table and it's fairly easy to do, trick is that you have to update it to something that currently doesn't exist as a key, and then back, since you can't increment it by 1 because that key already exists. It takes 2 updates, for a table with IDs smaller than 100 for example:

update my_table set id = id+100;
update my_table set id = id-99;

Upvotes: 1

Related Questions