"这篇文章主要介绍了在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优化等领域,理解并正确使用这个命令对于数据库管理是至关重要的。
<P>想要将值插入到自动编号(或者说是标识列,IDENTITY)中去,需要设定 SET IDENTITY_INSERT</P>
<P>示例:</P>
<P>1.首先建立一个有标识列的表:<BR>CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))</P>
<P>2.尝试在表中做以下操作:<BR>INSERT INTO products (id, product) VALUES(3, 'garden shovel')</P>
<P>结果会导致错误:“当 IDENTITY_INSERT 设置为 OFF 时,不能向表 'products' 中的标识列插入显式值。”</P>
<P>3.改用:<BR>SET IDENTITY_INSERT products ON<BR>INSERT INTO products (id, product) VALUES(1, 'garden shovel')</P>
<P>返回正确。</P>
<P>4.建立另外一个表products2,尝试相同插入操作:<BR>CREATE TABLE products2 (id int IDENTITY PRIMARY KEY, product varchar(40))</P>
<P>然后执行:<BR>SET IDENTITY_INSERT products2 ON<BR>INSERT INTO products2 (id, product) VALUES(1, 'garden shovel')</P>
<P>导致错误:“表 'material.dbo.products' 的 IDENTITY_INSERT 已经为 ON。无法对表 'products2' 执行 SET 操作。”</P>
<P>改为执行:<BR>SET IDENTITY_INSERT products OFF<BR>SET IDENTITY_INSERT products2 ON<BR>INSERT INTO products2 (id, product) VALUES(2, 'garden shovel')</P>
<P>执行通过。</P>
<P>5.尝试以下操作:<BR>SET IDENTITY_INSERT products2 ON<BR>INSERT INTO products2 SELECT * FROM products</P>
<P>导致错误:“仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'products2' 中为标识列指定显式值。”</P>
<P>6.改为:<BR>SET IDENTITY_INSERT products2 ON<BR>INSERT INTO products2 (id, product) SELECT * FROM products</P>
<P>执行通过。</P>
<P><STRONG>总结:</STRONG></P>
<P>1.每一次连接会话中的任一时刻,只能对一个表设置IDENTITY_INSERT ON,且设置只对当前会话有效;<BR>2.在对标识列执行插入操作进,一定要列出此标识列(当然,同时也就需要列出相关的其他列了)。</P>
<P>附:<BR>SQL Server帮助文档相关内容</P>
<H1><A name=_set_identity_insert></A><SPAN style="FONT-SIZE: 13px">SET IDENTITY_INSERT</SPAN></H1>
<P>允许将显式值插入表的标识列中。</P>
<H5>语法</H5>
<P>SET IDENTITY_INSERT [ <EM>database</EM><STRONG>.</STRONG>[ <EM>owner</EM><STRONG>.</STRONG>] ] {<EM>table</EM> } { ON | OFF }</P>
<H5>参数</H5>
<P><EM>database</EM></P>
<P class=indent>是表所有者的名称。</P>
<P><EM>table</EM></P>
<P> </P>
<P>如果插入值大于表的当前标识值,则 SQL Server 自动将新插入值作为当前标识值使用。</P>
下载后可阅读完整内容,剩余1页未读,立即下载
- 粉丝: 5
- 资源: 246
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- WebLogic集群配置与管理实战指南
- AIX5.3上安装Weblogic 9.2详细步骤
- 面向对象编程模拟试题详解与解析
- Flex+FMS2.0中文教程:开发流媒体应用的实践指南
- PID调节深入解析:从入门到精通
- 数字水印技术:保护版权的新防线
- 8位数码管显示24小时制数字电子钟程序设计
- Mhdd免费版详细使用教程:硬盘检测与坏道屏蔽
- 操作系统期末复习指南:进程、线程与系统调用详解
- Cognos8性能优化指南:软件参数与报表设计调优
- Cognos8开发入门:从Transformer到ReportStudio
- Cisco 6509交换机配置全面指南
- C#入门:XML基础教程与实例解析
- Matlab振动分析详解:从单自由度到6自由度模型
- Eclipse JDT中的ASTParser详解与核心类介绍
- Java程序员必备资源网站大全