Click here to Skip to main content
15,870,165 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a simple database table :employee(id int identity(1,1), name varchar(40))


It has 5 records


ID Name
---------------
1 A
2 B
3 C
4 D
5 E


I deleted last 2 records.
Now , if i insert, the next record will be allocate '6' to the 'ID' column. However , i want it to insert '4'. HOw should i do it..

Note : I cant modify/disable Identity property.

PLease help.. Client will eat my job as i have to give some demo but this issue is still not resolved...
Posted

you can use below statement if you have only three record in your table


DBCC CHECKIDENT (tableName, reseed, 4)
 
Share this answer
 
Comments
shikhar gilhotra 8-Jun-12 13:02pm    
Super ..you rock !!
To insert a value into the identity column, you will need to follow this pattern:

SQL
SET IDENTITY_INSERT myTable ON
INSERT INTO employee (id, name) VALUES(4, ‘D′)
SET IDENTITY_INSERT myTable OFF


That will allow you to insert a new record with an old ID. However, this is highly discouraged practice. Unless you are re-adding a record that you accidentally deleted or you are adding existing data to a newly-created table, you should really just allow the identity to be created with the next number. Only one record should ever have that identity value. A new record should not get an old (even unused) value.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900