语言吧技术网欢迎您投稿,程序员的世界我懂你!=====>点此登录投搞
当前位置: 首页 >>数据库 >>MSSQL >>SET IDENTITY_INSERT设置插入值到自动编号(或者说是标识列,IDENTITY)中的方

SET IDENTITY_INSERT设置插入值到自动编号(或者说是标识列,IDENTITY)中的方

2018/11/8 15:37:16 0人评论 217次 作者:admin

任何时候,会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON,并且为另一个表发出了 SET IDENTITY_INSERT ON 语句,则 Microsoft? SQL Server? 返回一个错误信息,指出 SET IDENTITY_INSE…

示例:


set identity_insert S_User_S ON

insert into S_User_S(StudentId,Mobile)

select 10021427,'XY01034X29'

set identity_insert S_User_S OFF



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


是指定的表所驻留的数据库名称。


owner


是表所有者的名称。


table


是含有标识列的表名。


注释

任何时候,会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON,并且为另一个表发出了 SET IDENTITY_INSERT ON 语句,则 Microsoft? SQL Server? 返回一个错误信息,指出 SET IDENTITY_INSERT 已设置为 ON 并报告此属性已设置为 ON 的表。


如果插入值大于表的当前标识值,则 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'

GO


SELECT * 

FROM products

GO


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

GO


-- Attempt to insert an explicit ID value of 3

INSERT INTO products (id, product) VALUES(3, 'garden shovel').

GO


SELECT * 

FROM products

GO

-- Drop products table.

DROP TABLE products

GO


转: http://www.cnblogs.com/kingjiong/archive/2010/02/08/1665949.html


Hadoop技术交流群:108396367


相关文章

CopyRight:2007-2018 语言吧 备案ICP:湘ICP备09009000号-4 http://www.yuyanba.com
分享按钮