CODEWITHSUNDEEP
sqlsql-serversql-server-2008
david sam
david sam

Reputation: 531

How change auto increment statement in SQL Server?

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?

Upvotes: 5

Views: 6771

Answers (3)

Urvesh Purohit
Urvesh Purohit

Reputation: 18

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.

Upvotes: -2

Tom V
Tom V

Reputation: 1496

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

Upvotes: 2

Arun Palanisamy
Arun Palanisamy

Reputation: 5469

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

Upvotes: 0

Related Questions