IDENTITY が設定されている列に INSERT を行う

現在表示しているページは選択中の表示言語には対応しておりません。 ×

SQL Server にはレコードを追加した際に特定の列に対して自動的に 1 からの連番を設定してくれる「IDENTITY」プロパティというものがあります。例えば「A」列に IDENTITY を設定し、３レコード追加すると、追加した順に A 列に「1」「2」「3」と自動的に値が設定されます。

INSERT 文を使った場合も自動的に値が設定されるので明示的に値を設定する必要はありませんが、外部キーなどに使用していて明示的に値を設定したい場合もあるかと思います。

例として図のように「ID」列に「IDENTITY」プロパティを設定したテーブルがあったとします。

このテーブルに対して「ID」列を含めた値を挿入する INSERT 文は以下のようになると思います。

insert into [テーブル名] ([ID], [Value]) values (1, 1)

しかし、実行してみると以下のエラーが出ると思います。

メッセージ 544、レベル 16、状態 1、行 1 IDENTITY_INSERT が OFF に設定されているときは、テーブル 'XXXXX' の ID 列に明示的な値を挿入できません。

本来 IDENTITY が設定されている列は自動で値が設定されていることを想定しているため、明示的に値を設定することはできないようになっています。

どうしても設定したい場合は、エラーメッセージにあるように「IDENTITY_INSERT」を「ON」にする必要があります。次の SQL のように INSERT する前に「IDENTITY_INSERT」を「ON」にし、INSERT が終わったら「OFF」に戻します。

set identity_insert [テーブル名] on;
insert into [テーブル名] ([ID], [Value]) values (1, 1)
set identity_insert [テーブル名] off;

-- 確認
select * from [テーブル名];

これで明示的に値を設定して INSERT できるようになります。テーブルを使った INSERT-SELECT も有効です。

IDENTITY を設定した列は自動で 1 から値が割り当てられますが、上記のように明示的に値を設定した場合、次は何番から割り当てられるのか気になるかと思います。この場合、次に割り当てられる値はテーブルに追加された値の中で最も大きな値の次の番号から割り当てられますので、新規にレコードを挿入しても値が重複することはありません。(ただし、障害等による例外はあります)