INSERT EXECステートメントを使用したIDENTITYカラムへの値挿入

2024-04-02

SQL Server でIDENTITYカラムに明示的な値を指定する

An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON.

概要:

このエラーメッセージは、IDENTITYプロパティを持つカラムに明示的な値を挿入しようとしたときに発生します。IDENTITYカラムは、通常、自動的に値が生成されますが、特定の状況では、明示的な値を指定することもできます。

原因:

このエラーメッセージの主な原因は、以下のいずれかです。

IDENTITYカラムに明示的な値を挿入しようとしているが、IDENTITY_INSERT設定が無効になっている。
IDENTITYカラムを含む列リストを指定していない。

解決方法:

このエラーメッセージを解決するには、以下のいずれかの方法を実行する必要があります。

IDENTITY_INSERT設定を有効にする:

SET IDENTITY_INSERT テーブル名 ON;

-- データ挿入

SET IDENTITY_INSERT テーブル名 OFF;

INSERT INTO テーブル名 (ID, カラム名1, カラム名2)
VALUES (1, 値1, 値2);

補足:

IDENTITYカラムに明示的な値を挿入する必要があるのは、稀なケースです。通常は、自動的に値を生成させる方が効率的です。
IDENTITY_INSERT設定を有効にする場合は、データ挿入後に必ず無効にすることを忘れないでください。

USE AdventureWorks2019;

-- IDENTITY_INSERT設定を有効にする
SET IDENTITY_INSERT Person.Address ON;

-- データ挿入
INSERT INTO Person.Address (AddressID, AddressLine1, City, StateProvinceID, PostalCode)
VALUES (1000, '123 Main Street', 'Anytown', 50, '12345');

-- IDENTITY_INSERT設定を無効にする
SET IDENTITY_INSERT Person.Address OFF;

-- 確認
SELECT * FROM Person.Address WHERE AddressID = 1000;

IDENTITYカラムを含む列リストを指定して、IDENTITYカラムに明示的な値を挿入する例:

USE AdventureWorks2019;

INSERT INTO Person.Address (ID, AddressLine1, City, StateProvinceID, PostalCode)
VALUES (1001, '456 Elm Street', 'Anytown', 50, '12345');

-- 確認
SELECT * FROM Person.Address WHERE ID = 1001;

上記サンプルコードは、AdventureWorks2019データベースを使用しています。

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

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

USE AdventureWorks2019;

DECLARE @ID INT;

SET @ID = 1002;

EXEC (@ID, '789 Oak Street', 'Anytown', 50, '12345');

-- 確認
SELECT * FROM Person.Address WHERE ID = 1002;

USE AdventureWorks2019;

MERGE Person.Address AS T
USING (VALUES (1003, '1011 Main Street', 'Anytown', 50, '12345')) AS S
ON T.AddressID = S.AddressID
WHEN MATCHED THEN
UPDATE SET
T.AddressLine1 = S.AddressLine1,
T.City = S.City,
T.StateProvinceID = S.StateProvinceID,
T.PostalCode = S.PostalCode
WHEN NOT MATCHED THEN
INSERT (AddressID, AddressLine1, City, StateProvinceID, PostalCode)
VALUES (S.AddressID, S.AddressLine1, S.City, S.StateProvinceID, S.PostalCode);

-- 確認
SELECT * FROM Person.Address WHERE ID = 1003;