SQL Server自增长列插入指定值--SET IDENTITY_INSERT ON|OFF(2)

 

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