SET IDENTITY_INSERT 学习心得【转载】

本文介绍了在SQL Server中如何正确地向带有自动编号(标识列)的表中插入特定值,包括设置SETIDENTITY_INSERT的正确用法,以及一些常见的错误示例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

此文转载自:http://hi.baidu.com/ebiyuan/blog/item/6b511987c030fe2bc75cc360.html

 

想要将值插入到自动编号(或者说是标识列,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_ownerdb_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
<think>我们正在处理用户关于SQL ServerSETIDENTITY_INSERT的使用方法的问题。根据用户需求,用户想启用对表的显式插入到自增列的功能。引用[3]中明确说明了IDENTITY_INSERT的用法:SETIDENTITY_INSERT表名 ON:开启对标识列显示值插入模式,允许手动插入数据到标识列。SETIDENTITY_INSERT表名 OFF:关闭,标识列不允许手动插入显示值。使用步骤:1.在执行显式插入到自增列之前,先执行SET IDENTITY_INSERT表名ON。2.执行插入操作,此时可以指定自增列的值。3.插入完成后,建议执行SET IDENTITY_INSERT表名OFF来关闭,以避免影响后续操作。注意事项:-一次只能对一个表设置IDENTITY_INSERTON,并且该表必须已经存在自增列(标识列)。-在插入时,必须显式列出所有列名,包括自增列。-插入的值必须在自增列的数据类型范围内,且不能与现有值冲突(除非自增列不是主键或唯一约束,但通常自增列是主键)。示例:假设有一个表T1,其中ID是自增列(标识列),现在要显式插入一个ID值。```sql--开启IDENTITY_INSERTSETIDENTITY_INSERTT1ON;--插入数据,显式指定ID值INSERTINTO T1 (ID,Name,...)VALUES(10, 'Test', ...);--这里指定ID为10--关闭IDENTITY_INSERTSETIDENTITY_INSERTT1OFF;```引用[2]中提到,在springboot集成达梦数据库时也遇到了类似问题,但注意达梦数据库的语法与SQLServer类似,但并非完全相同。用户的问题是针对SQL Server的,所以我们以SQL Server为准。另外,引用[1]中提到,非必要情况不建议操作,因为自增列通常由系统自动生成,手动插入可能导致重复或序列不一致。因此,使用此功能时需谨慎。根据上述信息,我们给出详细步骤和示例。生成相关问题:1.如何避免在显式插入自增列时出现重复值?2.使用SETIDENTITY_INSERTON时需要注意哪些事项?3.如何重置自增列的下一个值?</think>### SQL Server SET IDENTITY_INSERT 使用方法详解 #### 核心功能说明 当需要手动向自增列(标识列)插入特定值时,需使用 `SET IDENTITY_INSERT` 命令。其语法规则为: ```sql SET IDENTITY_INSERT [数据库名].[架构名].表名 ON; -- 开启显式插入 -- 执行插入操作... SET IDENTITY_INSERT [数据库名].[架构名].表名 OFF; -- 关闭显式插入 ``` #### 操作步骤示例 1. **开启显式插入模式** ```sql SET IDENTITY_INSERT dbo.Employees ON; -- 开启对Employees表的标识列插入 ``` 2. **执行显式插入操作** ```sql INSERT INTO dbo.Employees (EmployeeID, Name, Department) VALUES (1001, '张三', '技术部'); -- 必须显式列出列名(包括自增列) ``` > 📌 关键要求:必须显式指定所有插入列名,否则报错 3. **立即关闭显式插入** ```sql SET IDENTITY_INSERT dbo.Employees OFF; -- 操作完成后必须关闭 ``` #### 注意事项 1. **会话限制** 每个会话(Session)同一时间只能对一个表开启 `IDENTITY_INSERT`,开启新表前需关闭前一个表的设置[^3]。 2. **权限要求** 用户需具备表的 `ALTER` 权限才能执行此操作。 3. **值范围约束** 手动插入的值必须符合列的数据类型,且不能与现有标识值冲突(如主键冲突)。 4. **事务处理建议** 建议在事务中操作以保证数据一致性: ```sql BEGIN TRANSACTION; SET IDENTITY_INSERT dbo.Employees ON; INSERT INTO dbo.Employees (EmployeeID, ...) VALUES (...); SET IDENTITY_INSERT dbo.Employees OFF; COMMIT TRANSACTION; ``` #### 典型应用场景 - 数据迁移时保留原系统ID - 修复因删除数据导致的标识列断层 - 同步不同数据库的关联数据 > ⚠️ 警告:频繁使用可能导致标识列序列混乱,非必要场景不建议使用[^1]。常规开发应依赖数据库自动生成标识值。 --- ### 相关问题 1. 如何避免 `SET IDENTITY_INSERT ON` 时出现主键冲突? 2. 开启 `IDENTITY_INSERT` 后是否影响自增种子(SEED)的后续分配? 3. 在多用户并发环境下如何安全使用 `SET IDENTITY_INSERT`? 4. 如果忘记执行 `SET IDENTITY_INSERT OFF` 会造成什么后果? 5. 如何检查当前会话中哪些表启用了 `IDENTITY_INSERT` 状态? [^1]: 非必要情况不建议操作自增列 [^3]: 一次只能开启一个表的标识列插入模式
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值