如何手动插入标识？

CREATE TABLE masterTbl (
id INT IDENTITY(1,1) CONSTRAINT pk_id PRIMARY KEY,
name VARCHAR(100))

INSERT INTO masterTbl VALUES ('ABC', 'XYZ','PQR')

Now

现在

Select * FROM masterTbl

Result:

结果：

id  | name
---------------------
1 |  ABC
2 |  XYZ
3 |  PQR

DELETE FROM masterTbl WHERE id=1

Now

现在

Select * FROM masterTbl

Result:

结果：

id |  name
---------------------
2 |  XYZ
3 |  PQR

Now I want to run:

现在我要运行：

INSERT INTO masterTbl (id, name) VALUES (1, 'MNO')

How is this possible?

这怎么可能？

3 个解决方案

#1

In it's simplest form, you need to temporarily allow the insertion of identity values

在它最简单的形式中，您需要暂时允许插入标识值

SET IDENTITY_INSERT masterTbl ON
INSERT INTO masterTbl (id, name) VALUES (1, 'MNO')
SET IDENTITY_INSERT masterTbl OFF

#2

Yes you can set identity fields manually executing

是的，您可以设置手动执行的标识字段

SET IDENTITY_INSERT masterTbl ON

then insert your data

然后插入您的数据

INSERT INTO masterTbl (id, name) VALUES (1, 'MNO') 
......

and remember to call

并记得打电话

SET IDENTITY_INSERT masterTbl OFF

to reenable the correct functionality

重新启用正确的功能

#3

SET IDENTITY_INSERT masterTbl ON

http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx

#1