MS-SQL / SQL Server
SET IDENTITY_INSERT で IDENTITY を設定した列に値を登録する方法
列にIDENTITYを設定して自動的に連番が登録されるようにするのは一般的に使ってますよね。
だけど、その自動的に登録される連番を手動で登録した場合があります。
その時はどうするのか！！について説明します。
まず、IDENTITYを設定したテーブルを作成してデータを登録してみます。
-- #### テーブル作成
CREATE TABLE dbo.TempData
(
Idx int identity(1,1)
, Data1 varchar(10)
)
-- #### データ登録
INSERT INTO dbo.TempData (Data1) VALUES ('Test1')
INSERT INTO dbo.TempData (Data1) VALUES ('Test2')
では、データを確認してみましょう。
SELECT * FROM dbo.TempData
実行結果
Idx Data1
----------- ----------
1 Test1
2 Test2
Idx列にIDENTITYを設定したため、自動的に連番が登録されています。
では、Idx列にいきなり「10」番のデータで登録したいので下記のように作成してみました。
INSERT INTO dbo.TempData (Idx, Data1) VALUES (10, 'Test10')
問題なさそうですよね。
このまま実行すると下記のようなエラーが発生します。
メッセージ 544、レベル 16、状態 1、行 1
IDENTITY_INSERT が OFF に設定されているときは、テーブル 'TempData' の ID 列に明示的な値を挿入できません。
IDENTITYが設定されている列に手動でデータを登録する設定が「OFF」になっている意味です。
では、「ON」にしてデータを登録してみましょう。
SET IDENTITY_INSERT dbo.TempData ON
INSERT INTO dbo.TempData (Idx, Data1) VALUES (10, 'Test10')
上記のSQLを実行すると先程はエラーになって登録されなかったSQLが正常に実行されました。
では、データを確認してみましょう。
SELECT * FROM dbo.TempData
実行結果
Idx Data1
----------- ----------
1 Test1
2 Test2
10 Test10
Idx値に「10」として登録されました。
では、Idx列に連番で入れたいので、INSERT INTO する時に「Idx」部分を削除して登録してみます。
INSERT INTO dbo.TempData (Data1) VALUES ('Test4')
あっ！
エラーが発生しました！！！
メッセージ 545、レベル 16、状態 1、行 1
IDENTITY_INSERT が ON に設定されているか、レプリケーション ユーザーが NOT FOR REPLICATION ID 列に挿入しているときは、テーブル 'TempData' の ID 列には明示的な値を指定してください。
今回は「IDENTITY_INSERT」が「ON」になっているからエラーが発生しました。
「IDENTITY_INSERT」を「ON」にするとINSERTする時の連番機能が無効化されますので、指定した番号が登録できるのです。
では、連番機能を有効化する為には「IDENTITY_INSERT」を「OFF」にする必要があります。
下記のSQLを実行して「IDENTITY_INSERT」を「OFF」して「Idx」列には自動的に連番が入るようにします。
そしてデータを登録してみましょう。
SET IDENTITY_INSERT dbo.TempData OFF
INSERT INTO dbo.TempData (Data1) VALUES ('Test4')
今回は問題なくデータが登録されました。
では、データを確認してみましょう。
SELECT * FROM dbo.TempData
実行結果
Idx Data1
----------- ----------
1 Test1
2 Test2
10 Test10
11 Test4
最後に登録した「Test4」データの連番の「Idx」値が「11」になっています。
「3」に入るんじゃないの？？って思うかも知れませんが、
連番はそのテーブルのIDENTITY値の最大値が次の番号で使用されますので、
「11」で登録されました。