SQL IDENTITY INSERT

SQL Server 中使用示例向标识列插入值的说明。为此，我们将使用数据库中的以下 Customer 表。

从下面的代码片段中，您可以观察到 [Customer Key] 列是一个标识列。我们的工作是向此标识列 [Customer Key] 插入值，即，标识插入。

CREATE TABLE [Customer]
(
  [CustomerKey] [int] IDENTITY(1,1) NOT NULL,
  [FirstName] [varchar](50) NULL,
  [LastName] [varchar](50) NULL,
  [BirthDate] [date] NULL,
  [EmailAddress] [nvarchar](50) NULL,
  [Yearly Income] [money] NULL,
  [Profession] [nvarchar](100) NULL
)

在 SQL 中向标识列插入值

在我们开始使用命令之前，让我们看看使用以下语句将值插入标识列（Customer Key）时会发生什么。

INSERT INTO [Customer] 
  ([CustomerKey], [FirstName],[LastName],[BirthDate],[EmailAddress],[Yearly Income],[Profession])
VALUES (1, 'John', 'Miller', '2006-01-28', 
        'sql@tutorialgateway.org', 20000, 'Developer')

运行上述查询以将值插入标识列

Messages
--------
Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Customer' when it is set to OFF.

从上图可以看出，它抛出了一个错误，消息为：当 SQL IDENTITY_INSERT 设置为 OFF 时，无法为表 ‘Customer’ 的标识列插入显式值。这是因为任何使用 IDENTITY 属性的列都将基于起始位置，并根据表中插入的每一行的增量值自动递增。

让我们看看，当我们不包括标识列（customer key）并向 Server 插入剩余值时会发生什么。

-- Example
INSERT INTO [Customer] 
  ([FirstName],[LastName],[BirthDate],
   [EmailAddress],[Yearly Income],[Profession])
VALUES ('John', 'Miller', '2006-01-28', 
        'sql@tutorialgateway.org', 20000, 'Developer')
Messages
--------
(1 row(s) affected)

从上图可以看出，受影响了 1 行。现在，让我们使用以下查询看看上述语句是否将值插入了 Customer 表。

SELECT * FROM [Customer]

从下图可以看出，由于 IDENTITY，Customer Key 值 1 被自动插入。

IDENTITY INSERT 3

SET IDENTITY_INSERT ON / OFF

在此插入示例中，我们将使用 SET IDENTITY_INSERT 属性将列的 IDENTITY 设置为 ON 和 OFF。

--  Example
SET IDENTITY_INSERT [Customer] ON  

INSERT INTO [Customer] 
  ([CustomerKey], [FirstName],[LastName],[BirthDate],
   [EmailAddress],[Yearly Income],[Profession])
VALUES (100, 'Tutorial', 'Gateway', '2015-08-31', 
         'contact@tutorialgateway.org', 50000, 'Founder')

SET IDENTITY_INSERT [Customer] OFF
SET IDENTITY_INSERT ON | OFF

以下语句将在 Customers 表上将 SQL Identity Insert 设置为 ON。

SET IDENTITY_INSERT [Customer] ON

以下语句将把显式值插入 Customer 表。它包括 IDENTITY 列 [Customer Key]

INSERT INTO [Customer] 
  ([CustomerKey], [FirstName],[LastName],[BirthDate],
   [EmailAddress],[Yearly Income],[Profession])
VALUES (100, 'Tutorial', 'Gateway', '2015-08-31', 
         'contact@tutorialgateway.org', 50000, 'Founder')

以下语句将在 Customers 表上将 Identity_Insert 设置为 OFF。请记住，在此语句之后，您将无法将值插入标识列（此处为 [Customer Key]）。

SET IDENTITY_INSERT [Customer] OFF

使用下面的查询检查上述语句是否将值插入了标识列。

SELECT * FROM [Customer]

您可以看到我们已成功将值插入标识列。

IDENTITY INSERT 5

一个会话只允许一个表将其 IDENTITY_INSERT 属性设置为 ON。如果您尝试在多个表上发出 SET IDENTITY_INSERT ON 语句，SSMS 将返回错误消息。

评论已关闭。