当 IDENTITY_INSERT 设置为 OFF 时,不能向表 ‘student’ 中的标识列插入显式值。

  • Post author:
  • Post category:其他



想要将值插入到自动编号(或者说是标识列,

IDENTITY


)中去,需要设定


SET

IDENTITY_INSERT


示例:


1.

首先建立一个有标识列的表:




CREATE

TABLE

products

(id

int

IDENTITY

PRIMARY

KEY,

product

varchar(40))


2.

尝试在表中做以下操作:




INSERT

INTO

products

(id,

product)

VALUES(3,

‘garden

shovel’)


结果会导致错误:








IDENTITY_INSERT



设置为



OFF



时,不能向表



‘products’



中的标识列插入显式值。





3.

改用:




SET

IDENTITY_INSERT

products

ON



INSERT

INTO

products

(id,

product)

VALUES(1,

‘garden

shovel’)


返回正确。


4.

建立另外一个表


products2


,尝试相同插入操作:




CREATE

TABLE

products2

(id

int

IDENTITY

PRIMARY

KEY,

product

varchar(40))


然后执行:



SET

IDENTITY_INSERT

products2

ON



INSERT

INTO

products2

(id,

product)

VALUES(1,

‘garden

shovel’)


导致错误:








‘material.dbo.products’







IDENTITY_INSERT



已经为



ON


。无法对表



‘products2’



执行



SET



操作。





改为执行:



SET

IDENTITY_INSERT

products

OFF



SET

IDENTITY_INSERT

products2

ON



INSERT

INTO

products2

(id,

product)

VALUES(2,

‘garden

shovel’)


执行通过。


5.

尝试以下操作:




SET

IDENTITY_INSERT

products2

ON



INSERT

INTO

products2

SELECT

*

FROM

products


导致错误:




仅当使用了列的列表,并且



IDENTITY_INSERT







ON



时,才能在表



‘products2’



中为标识列指定显式值。





6.

改为:




SET

IDENTITY_INSERT

products2

ON



INSERT

INTO

products2

(id,

product)

SELECT

*

FROM

products


执行通过。


总结:


1.

每一次连接会话中的任一时刻,只能对一个表设置


IDENTITY_INSERT

ON


,且设置只对当前会话有效;




2.

在对标识列执行插入操作进,一定要列出此标识列(当然,同时也就需要列出相关的其他列了)。


附:



SQL

Server


帮助文档相关内容


SET

IDENTITY_INSERT




允许将显式值插入表的标识列中。


语法



SET

IDENTITY_INSERT

[

database

.

[

owner

.

]

]

{

table

}

{

ON

|

OFF

}


参数



database


是表所有者的名称。


table


如果插入值大于表的当前标识值,则


SQL

Server



自动将新插入值作为当前标识值使用。


SET

IDENTITY_INSERT


的设置是在执行或运行时设置,而不是在分析时设置。


权限



执行权限默认授予


sysadmin



固定服务器角色和



db_owner







db_ddladmin



固定数据库角色以及对象所有者。


示例



下例创建一个含有标识列的表,并显示如何使用


SET

IDENTITY_INSERT



设置填充由



DELETE



语句导致的标识值中的空隙。




Create

products

table.CREATE

TABLE

products

(id

int

IDENTITY

PRIMARY

KEY,

product

varchar(40))GO–

Inserting

values

into

products

table.INSERT

INTO

products

(product)

VALUES

(‘screwdriver’)INSERT

INTO

products

(product)

VALUES

(‘hammer’)INSERT

INTO

products

(product)

VALUES

(‘saw’)INSERT

INTO

products

(product)

VALUES

(‘shovel’)GO–

Create

a

gap

in

the

identity

values.DELETE

products

WHERE

product

=

‘saw’GOSELECT

*

FROM

productsGO–

Attempt

to

insert

an

explicit

ID

value

of

3;–

should

return

a

warning.INSERT

INTO

products

(id,

product)

VALUES(3,

‘garden

shovel’)GO–

SET

IDENTITY_INSERT

to

ON.SET

IDENTITY_INSERT

products

ONGO–

Attempt

to

insert

an

explicit

ID

value

of

3INSERT

INTO

products

(id,

product)

VALUES(3,

‘garden

shovel’).GOSELECT

*

FROM

productsGO–

Drop

products

table.DROP

TABLE

productsGO


总结:


1.

每一次连接会话中的任一时刻,只能对一个表设置


IDENTITY_INSERT

ON


,且设置只对当前会话有效;




2.

在对标识列执行插入操作进,一定要列出此标识列(当然,同时也就需要列出相关的其他列了)。



版权声明:本文为comeonJ原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。