想要将值插入到自动编号(或者说是标识列,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
执行通过。
7.IDENTITY列不一定非要是主键或唯一键列,实际上作为IDENTITY的列只要求不能为空,但是IDENTITY列默认情况下是可以有重复值的,我们来看下面的SQL语句:
CREATE TABLE [dbo].[DemoPerson]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL, [Age] [int] NULL ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[DemoPerson] ON INSERT INTO [dbo].[DemoPerson]([ID],[Name],[Age]) VALUES (1,N'Jack',31), (1,N'Jack',31), (2,N'Bob',32), (2,N'Bob',32), (3,N'Sam',33), (3,N'Sam',33) SET IDENTITY_INSERT [dbo].[DemoPerson] OFF SELECT * FROM [dbo].[DemoPerson] INSERT INTO [dbo].[DemoPerson]([Name],[Age]) VALUES (N'Tom',35) SELECT * FROM [dbo].[DemoPerson]
执行结果如下:
总结:
- 每一次连接会话中的任一时刻,只能对一个表设置IDENTITY_INSERT ON,且设置只对当前会话有效;
- 在对标识列执行插入操作进,一定要列出此标识列(当然,同时也就需要列出相关的其他列了)。
- 任何时候,会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON,并且为另一个表发出了 SET IDENTITY_INSERT ON 语句,则 Microsoft® SQL Server™ 返回一个错误信息,指出 SET IDENTITY_INSERT 已设置为 ON 并报告此属性已设置为 ON 的表。
- 如果插入值大于表的当前标识值,则 SQL Server 自动将新插入值作为当前标识值使用。
- SET IDENTITY_INSERT 的设置是在执行或运行时设置,而不是在分析时设置。