SQL Server自增长列插入指定值－－SET IDENTITY_INSERT ON|OFF[MSSQL防范]

　　想要将值插入到自动编号(大概说是标识列,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

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

　　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

　　作者 hehongwei44