SQL中设置IDENTITY_INSERT详解

需积分: 50 187 浏览量更新于2024-09-17 收藏 3KB TXT 举报

"这篇文章主要介绍了在SQL Server中如何使用`SET IDENTITY_INSERT`命令来处理具有标识列（IDENTITY）的表的数据插入操作。在标识列的表中，通常系统会自动为新插入的行生成唯一的标识符，但有时我们需要手动指定这个值，这时就需要使用`SET IDENTITY_INSERT`。" 在SQL Server中，当你创建一个表并包含一个标识列时（例如，`id int IDENTITY PRIMARY KEY`），这个列会被自动设置为自增的唯一值，不允许用户直接插入特定的值。然而，在某些情况下，比如数据迁移或复制数据到已有标识列的表中，我们可能需要覆盖这一默认行为，允许插入特定的ID值。此时，就需要使用`SET IDENTITY_INSERT`语句。 1. 创建一个带有标识列的表： ```sql CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40)) ``` 2. 尝试插入特定ID的行（默认情况下会报错）： ```sql INSERT INTO products (id, product) VALUES (3, 'gardenshovel') ``` 因为`IDENTITY_INSERT`默认是`OFF`，所以这会导致错误。 3. 启用`IDENTITY_INSERT`插入特定ID： ```sql SET IDENTITY_INSERT products ON INSERT INTO products (id, product) VALUES (1, 'gardenshovel') ``` 这样就能成功插入一行，其中ID是我们指定的1。 4. 如果有另一个表`products2`，同样有标识列，并且尝试在未关闭对`products`的`IDENTITY_INSERT`的情况下启用它： ```sql SET IDENTITY_INSERT products2 ON INSERT INTO products2 (id, product) VALUES (1, 'gardenshovel') ``` 由于`IDENTITY_INSERT`在`products`上仍为`ON`，所以这会失败。必须先关闭`products`的`IDENTITY_INSERT`，然后再开启`products2`的。 5. 正确做法： ```sql SET IDENTITY_INSERT products OFF SET IDENTITY_INSERT products2 ON INSERT INTO products2 (id, product) VALUES (2, 'gardenshovel') ``` 这样可以正确地在`products2`中插入指定ID的行。 6. 当完成插入后，为了保持数据库的正常操作，应该将`IDENTITY_INSERT`设回`OFF`，防止后续误操作： ```sql SET IDENTITY_INSERT products2 OFF ``` 总结来说，`SET IDENTITY_INSERT`是SQL Server提供的一种临时改变表的行为，允许在具有标识列的表中插入自定义的ID值。在进行数据导入或特殊操作时，这个命令非常有用，但使用后记得恢复默认设置，以免影响系统自动递增的功能。在多表操作时，尤其要注意`IDENTITY_INSERT`的状态，避免冲突。在ASP.NET、Jquery和DBA优化等领域，理解并正确使用这个命令对于数据库管理是至关重要的。

来自：<A href="http://blog.csdn.net/hui_0924/article/details/6725064">http://blog.csdn.net/hui_0924/article/details/6725064</A> 
想要将值插入到自动编号（或者说是标识列，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帮助文档相关内容
<H1><A name=_set_identity_insert></A>SET IDENTITY_INSERT</H1>
允许将显式值插入表的标识列中。
<H5>语法</H5>
SET IDENTITY_INSERT [ database.[ owner.] ] {table } { ON | OFF }
<H5>参数</H5>
database
是表所有者的名称。
table
 
如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。

下载后可阅读完整内容，剩余1页未读，立即下载

wxz139292920

粉丝: 5
资源: 245

SQL中设置IDENTITY_INSERT详解

达梦数据库sql语言使用手册

C#.NET生成普通二维码方法及生成带图片二维码方法源码Demo

rip宣告网段选择版本

基于springboot+vue的学生选课系统（Java毕业设计，附源码，部署教程）.zip

基于BP神经网络的电力负荷预测-基于BP神经网络的短期电力负荷预测源码

基于springboot+vue的常规应急物资管理系统（Java毕业设计，附源码，部署教程）.zip

网站系统渗透测试报告.doc

I型NPC三电平逆变器 仿真 有三相逆变器参数设计，SVPWM，直流均压控制，双闭环控制说明文档（可加好友另算） SVPWM调制 中点电位平衡控制，LCL型滤波器 直流电压1200V，交流侧输出线电

燕山大学EDA综合实训实验报告.doc

一个基于qt实现钩子屏蔽系统按键、任务管理器、任务栏实例

最新资源

I型NPC三电平逆变器仿真有三相逆变器参数设计，SVPWM，直流均压控制，双闭环控制说明文档（可加好友另算） SVPWM调制中点电位平衡控制，LCL型滤波器直流电压1200V，交流侧输出线电