SET IDENTITY_INSERT的用法

如果将值插入到表的标识列中，需要启用 SET IDENTITY_INSERT。

举例如下：

创建表Orders、Products，Orders表与Products表分别有标识列OrderID与ProductID：

CREATE TABLE Orders(
OrderID    INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
PriceDate DateTime
)

CREATE TABLE Products(
ProductID    INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
PriceDate Datetime
)

当我们用如下插入语句往Orders表中插入数据：

INSERT INTO Orders (OrderID ,PriceDate ) VALUES(1,GETDATE())

执行此语句后，会出现如下错误：

把插入语句做如下修改：

SET IDENTITY_INSERT Orders ON
INSERT INTO Orders (OrderID ,PriceDate ) VALUES(1,GETDATE())

此时执行成功；

我们在向Products表做如上插入：

SET IDENTITY_INSERT Products ON
INSERT INTO Products (ProductID,PriceDate ) VALUES(1,GETDATE())

产生如下错误：

我们对sql做如下修改后，products表插入数据成功

 

SET IDENTITY_INSERT Orders ON
INSERT INTO Orders (OrderID ,PriceDate ) VALUES(1,GETDATE())
SET IDENTITY_INSERT Orders OFF

SET IDENTITY_INSERT Products ON
INSERT INTO Products (ProductID,PriceDate ) VALUES(1,GETDATE())
SET IDENTITY_INSERT Products OFF

可以看到当我们对表的Identity_Insert 的启用与关闭时成对出现，即

SET IDENTITY_INSERT Products ON
--Code
SET IDENTITY_INSERT Products OFF

如果我们用下面的语句往Products表中插入数据

SET IDENTITY_INSERT Products ON
INSERT INTO Products SELECT * FROM Orders
SET IDENTITY_INSERT Products OFF

出现如下错误：

修改上面t-sql语句：

SET IDENTITY_INSERT Products ON
INSERT INTO Products(ProductID,PriceDate) SELECT * FROM Orders
SET IDENTITY_INSERT Products OFF

可以知道如果对标识列进行插入，那么需要显示的列出标识列