エラーメッセージ「Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF」の解決方法

2024-04-02

エラーメッセージ「Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF」の解説

原因

IDENTITY カラムは、自動的に一意の値を生成するように設定されています。そのため、通常は明示的な値を挿入する必要はありません。

IDENTITY_INSERT プロパティは、IDENTITY カラムに明示的な値を挿入することを許可するかどうかを制御します。このプロパティが OFF になっている場合、IDENTITY カラムに明示的な値を挿入することはできません。

解決方法

このエラーメッセージを解決するには、以下の方法があります。

IDENTITY_INSERT プロパティを ON にする

SET IDENTITY_INSERT table ON;

INSERT INTO table (id, name) VALUES (1, 'John Doe');

SET IDENTITY_INSERT table OFF;

IDENTITY カラムに明示的な値を挿入しない

INSERT INTO table (name) VALUES ('John Doe');

IDENTITY カラムを自動生成しないようにする

CREATE TABLE table (
  id INT NOT NULL,
  name VARCHAR(50)
);

補足

IDENTITY_INSERT プロパティは、テーブルごとに設定する必要があります。
IDENTITY_INSERT プロパティを ON に設定すると、IDENTITY カラムに重複する値を挿入できるようになります。

このエラーメッセージに関する情報は、以下のサイトでも確認できます。

USE AdventureWorks2019;

-- IDENTITY_INSERT プロパティを ON に設定する
SET IDENTITY_INSERT Person.Address ON;

-- IDENTITY カラムに明示的な値を挿入する
INSERT INTO Person.Address (AddressID, FirstName, LastName)
VALUES (1000, 'John', 'Doe');

-- IDENTITY_INSERT プロパティを OFF に設定する
SET IDENTITY_INSERT Person.Address OFF;

-- IDENTITY カラムに明示的な値を挿入しようとすると、エラーが発生する
INSERT INTO Person.Address (AddressID, FirstName, LastName)
VALUES (1001, 'Jane', 'Doe');

GO

-- エラーメッセージ:
-- Cannot insert explicit value for identity column in table 'Person.Address' when IDENTITY_INSERT is set to OFF.

このコードを実行すると、最初の INSERT ステートメントは成功しますが、2 番目の INSERT ステートメントはエラーになります。

以下のコードは、IDENTITY カラムに明示的な値を挿入しない方法を示しています。

USE AdventureWorks2019;

INSERT INTO Person.Address (FirstName, LastName)
VALUES ('John', 'Doe');

GO

-- AddressID カラムには、自動的に一意の値が生成されます
SELECT AddressID, FirstName, LastName
FROM Person.Address
WHERE FirstName = 'John'
AND LastName = 'Doe';

GO

このコードを実行すると、AddressID カラムには自動的に 1001 という値が生成されます。

補足

このコードは、SQL Server 2019 で実行されています。
コードを実行する前に、AdventureWorks2019 データベースを復元する必要があります。

IDENTITY カラムに明示的な値を挿入するその他の方法

INSERT EXEC ステートメントを使用する

USE AdventureWorks2019;

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'INSERT INTO Person.Address (AddressID, FirstName, LastName)
VALUES (1000, ''John'', ''Doe'')';

EXEC sp_executesql @sql;

GO

このコードは、sp_executesql ストアドプロシージャを使用して、動的に INSERT ステートメントを実行します。

OUTPUT 句を使用する

USE AdventureWorks2019;

DECLARE @AddressID INT;

INSERT INTO Person.Address (FirstName, LastName)
OUTPUT INSERTED.AddressID
VALUES ('John', 'Doe');

SELECT @AddressID;

GO

このコードは、OUTPUT 句を使用して、INSERT ステートメントによって挿入された値を取得します。

MERGE ステートメントを使用する

USE AdventureWorks2019;

MERGE Person.Address AS T
USING (VALUES (1000, 'John', 'Doe')) AS S (AddressID, FirstName, LastName)
ON T.AddressID = S.AddressID
WHEN MATCHED THEN
UPDATE SET T.FirstName = S.FirstName, T.LastName = S.LastName
WHEN NOT MATCHED THEN
INSERT (AddressID, FirstName, LastName)
VALUES (S.AddressID, S.FirstName, S.LastName);

GO