SET IDENTITY_INSERT-学习心得

1.首先新建含有标识列的表：

Create table test_userInfo(id int identity primary key not null,name varchar(50) not null)--执行成功

2插入数据，一般我们使用：

insert test_userInfo (name) values('May')--执行成功

先执行5次，获得5行测试数据，再删除id=4的数据

delete From [TestDB].[dbo].[test_userInfo] where id=4--执行成功

3再按以上步骤新建表test_userInfo2

Create table test_userInfo(id int identity primary key not null,name varchar(50) not null)--执行成功

insert test_userInfo (name) values('May')--执行成功，执行5次。

delete From [TestDB].[dbo].[test_userInfo] where id=4--执行成功

4但是当我们插入数据时想指定自增id，或者想修补delete掉的（id=4）数据时，

错误1：

insert test_userInfo(id,name) values(4,'Alice')

--报错：当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'test_userInfo' 中的标识列插入显式值。

5这里我们就需要IDENTITY_INSERT ：

正确1：

set identity_insert test_userInfo on

insert test_userInfo(id,name) values(4,'Alice')

--执行成功。

正确2：

set identity_insert test_userInfo on

insert test_userInfo(id,name) values(14,'Alice')

--执行成功。id的值跳过了6-13，下次不指定id插入时，从id=15开始添加。

6然后对表test_userInfo2 继续执行：

错误2

set identity_insert test_userInfo2 on

insert test_userInfo2(id,name) values(4,'Alice')

--报错：表 'TestDB.dbo.test_userInfo' 的 IDENTITY_INSERT 已经为 ON。无法为表 'test_userInfo2' 执行 SET 操作。

7改为如下代码，或等候几分钟后可以通过：

正确2： 

set identity_insert test_userInfo off

set identity_insert test_userInfo2 on

insert test_userInfo2(id,name) values(4,'Alice')

--执行成功。id改为10等未启用的数据也同样成功。

8当我们在已有数据行插入时，会报错：

错误3：

set identity_insert test_userInfo on

insert test_userInfo(id,name) values(3,'Alice')

--报错：违反了 PRIMARY KEY 约束 'PK__test_use__3213E83F03317E3D'。不能在对象 'dbo.test_userInfo' 中插入重复键。

9当我们在不给定id时却set on，会报错：

错误4：

set identity_insert test_userInfo on

insert test_userInfo (name) values('May')

--报错：当 IDENTITY_INSERT 设置为 ON 或某个复制用户向 NOT FOR REPLICATION 标识列中插入内容时，必须为表 'test_userInfo' 中的标识列指定显式值。

10不给定id时要求set off：

正确3： 

set identity_insert test_userInfo off

insert test_userInfo (name) values('May')

--执行成功，且从最后添加数据。即从id=15开始。

注释
任何时候，会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON，并且为另一个表发出了 SET IDENTITY_INSERT ON 语句，则 Microsoft® SQL Server™ 返回一个错误信息，指出 SET IDENTITY_INSERT 已设置为 ON 并报告此属性已设置为 ON 的表。

如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。

SET IDENTITY_INSERT 的设置是在执行或运行时设置，而不是在分析时设置。

权限
执行权限默认授予 sysadmin 固定服务器角色和 db_owner 及 db_ddladmin 固定数据库角色以及对象所有者。