Modify an existing Identity Column Increment

Question

I'm at a dead end

I am trying to change the value of the Identity column increment in a collection of existing MS SQL tables (which all have data) and trying to investigate whether I can do without writing special scripts for each table. I cannot find a solution that does not require dropping and re-creating tables, each table of which requires a different script, since each of them has different column columns.

For example, I want to modify an 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]

For

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]

Through something like

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

Saving data.

This will fix the big deployment problem that I am facing right now, so any help would be appreciated

Answer 1

. DBCC Chekident. .

Answer 2

, , , , , , 1, . 2 , 100, :

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

Answer 3

, , , .

, - , , .

ActionType

, . , .

, , .

Answer 4

, , , , . , :

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

, - , :

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

...Add your data here

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

Answer 5

. . , identity(1,1) [ex A]

, increment of, , . [ex B]

, ids + , , . be

script , .

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

Answer 6

"", :

, , , , , , Excel , Excel Int. Int Identity

Identity , , , .

.

Identity , Int Dmax.

,