How change auto increment statement in SQL Server?

Question

I create a table:

create table empty 
(
    data_id int identity(70,30),
    emp_name varchar(20),
    address varchar(20),
    city varchar(20)
)

and insert data like:

data_id   emp_name      address        city 
---------------------------------------------
70        ritesh        210 b_block     sivapur
100       pritma        visvaas khand   lucknow
130       rakesh        nehru nagar     delhi

Now I want to alter auto increment from (70, 30) to (70, 10).

Can I do this?

The use of `identity` suggests that you are using SQL Server and not MySQL, so I removed the tag for the latter. — Gordon Linoff, Jul 30 '15 at 11:06
rename the existing table. create a new table with the same name as the old with the new seed. insert data from old table into new using the SET IDENTITY_INSERT ON so that existing keys are retained. — Jeremy, Jul 30 '15 at 11:42
Once a column is defined as `IDENTITY`, you cannot modify it. Neither can you add the `IDENTITY` specification to an existing column (or remove it from a column). — marc_s, Jul 30 '15 at 13:15

score 2 · Answer 1 · answered Jul 30 '15 at 11:16

You can not change that in one go, you would need to create a new table and copy over the rows.

Try this to create a temp table, move over the rows, drop the old table and rename the temp table:

BEGIN TRANSACTION

CREATE TABLE dbo.Tmp_empty
    (
    data_id int NOT NULL IDENTITY (70, 10),
    emp_name varchar(20) NULL,
    address varchar(20) NULL,
    city varchar(20) NULL
    )  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_empty ON
GO
IF EXISTS(SELECT * FROM dbo.empty)
     EXEC('INSERT INTO dbo.Tmp_empty (data_id, emp_name, address, city)
        SELECT data_id, emp_name, address, city FROM dbo.empty WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_empty OFF
GO
DROP TABLE dbo.empty
GO
EXECUTE sp_rename N'dbo.Tmp_empty', N'empty', 'OBJECT' 
GO
COMMIT

score 1 · Answer 2 · answered Jul 30 '15 at 11:13

1

You can use the DBCC CHECKIDENT

  DBCC CHECKIDENT 
 ( 
   table_name
    [, { NORESEED | { RESEED [, new_reseed_value ] } } ]
 )
  [ WITH NO_INFOMSGS ]

Eg DBCC CHECKIDENT ('empty', RESEED, 10);

Please refer this DBCC CHECKIDENT

answered Jul 30 '15 at 11:13

Arun Palanisamy

5,281
6
28
53

That doesn't change the increment, that resets the seed – Tom V Jul 30 '15 at 11:16

score -1 · Accepted Answer · edited Jul 30 '15 at 11:50

-1

Create a new column called data_id_New.

ALTER TABLE empty ADD data_id_New int;

Now copy all values of data_id into data_id_New.

Update empty set data_id_New = data_id

Now delete column.

ALTER TABLE empty DROP COLUMN data_id;

Rename column data_id_New to data_id.

ALTER TABLE empty RENAME COLUMN data_id_new to data_id;

Finally modify your new column

ALTER TABLE empty MODIFY data_id int identity(70,10);

Hope, this will solve your problem.

edited Jul 30 '15 at 11:50

Michele La Ferla

6,775
11
53
79

answered Jul 30 '15 at 11:35

Urvesh Purohit

20
4

3

You **cannot** (in SQL Server) use `ALTER TABLE .. MODIFY` - the command is `ALTER TABLE .... ALTER COLUMN ` but even with the correct syntax, you **CANNOT** add the `IDENTITY` specification to an existing column - just cannot be done. – marc_s Jul 30 '15 at 12:58

How change auto increment statement in SQL Server?

3 Answers3