How do I change the automatic increment operator in SQL Server?
I am creating 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 change the auto increment from (70, 30)
to (70, 10)
.
Can I do it?
Create a new column named data_id_New
.
ALTER TABLE empty ADD data_id_New int;
Now copy all values data_id
to data_id_New
.
Update empty set data_id_New = data_id
Now remove the column.
ALTER TABLE empty DROP COLUMN data_id;
Rename the column data_id_New
to data_id
.
ALTER TABLE empty RENAME COLUMN data_id_new to data_id;
Finally, change your new column
ALTER TABLE empty MODIFY data_id int identity(70,10);
Hope this solves your problem.
You cannot change this in one go, you will need to create a new table and copy the rows.
Try creating a temp table, step by line, 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
you can use DBCC CHECKIDENT
DBCC CHECKIDENT
(
table_name
[, { NORESEED | { RESEED [, new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]
Eg DBCC CHECKIDENT ('empty', RESEED, 10);
Please refer to DBCC CHECKIDENT