[原创] set IDENTITY_INSERT on 和 off 的设置

本文介绍在 SQL Server 中如何通过设置 IDENTITY_INSERT 来批量插入带有标识列的数据。包括开启和关闭标识符插入功能的具体命令,并给出实际的示例。

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

sqlserver 批量插入记录时,对有标识列的字段要设置 set IDENTITY_INSERT 表名 on,然后再执行插入记录操作;插入完毕后恢复为 off 设置

格式:
  set IDENTITY_INSERT 表名 on
  set IDENTITY_INSERT 表名 offf

举例:

set IDENTITY_INSERT peoplePworkpositiontype  on
insert peoplePworkpositiontype(id,workpositiontype,workpositiontypeid) values(1 , '平台' , 1 ) 
insert peoplePworkpositiontype(id,workpositiontype,workpositiontypeid) values(2 , '陆地' , 2 ) 
insert peoplePworkpositiontype(id,workpositiontype,workpositiontypeid) values(3 , '海上' , 3 )

go
set IDENTITY_INSERT peoplePworkpositiontype  off


set IDENTITY_INSERT peoplePstatetype  on
insert peoplePstatetype(id,nowstatetype,nowstatetypeid) values(1 , '出海' , 1 ) 
insert peoplePstatetype(id,nowstatetype,nowstatetypeid) values(2 , '出差' , 2 ) 
insert peoplePstatetype(id,nowstatetype,nowstatetypeid) values(3 , '公司' , 3 ) 
insert peoplePstatetype(id,nowstatetype,nowstatetypeid) values(4 , '会议' , 4 ) 
go
set IDENTITY_INSERT peoplePstatetype  off
go

 
<think>我们正在处理用户关于SQL Server中SETIDENTITY_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]: 一次只能开启一个表的标识列插入模式
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值