如何插入身份 [英] How to insert the identity

问题描述

我有一个简单的数据库表:employee(id int identity(1,1)，名称varchar(40))


它有5条记录


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


我删除了最后2条记录.
现在，如果我插入，下一条记录将被分配"6"到"ID"列.但是，我希望它插入"4".我应该怎么做..

注意:我无法修改/禁用Identity属性.

请帮忙..客户会吃掉我的工作，因为我必须提供一些演示，但是此问题仍未解决...

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...

推荐答案

如果您可以使用以下语句表中只有三个记录


DBCC CHECKIDENT(tableName，种子，4)

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


DBCC CHECKIDENT (tableName, reseed, 4)

要将值插入到Identity列，您将需要遵循以下模式:

To insert a value into the identity column, you will need to follow this pattern:

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

这样您就可以插入带有旧ID的新记录.但是，强烈建议不要这样做.除非您要重新添加意外删除的记录，或者将现有数据添加到新创建的表中，否则您实际上应该只允许使用下一个数字来创建标识.只有一条记录应该具有该标识值.新记录不应获得旧的(甚至未使用的)值.

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.

这篇关于如何插入身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！