MS-SQL / SQL Server
IDENTITY に任意の値を設定はSET IDENTITY_INSERTを使おう！
テーブルを作成する時にidentityを設定したテーブルがあると思いますが、
基本的にidentityが設定されているカラムにはデータを指定して登録することができません。
簡単にテストをしながら理解しましょう。
まず、IDENTITYを設定したテーブルを作成し、データを登録してみましょう。
-- #### テーブル作成
CREATE TABLE dbo.TempData
(
Sn int identity(1,1) not null
, StrVal varchar(10)
)
-- #### データ登録
INSERT INTO dbo.TempData (StrVal)
VALUES('test1')
INSERT INTO dbo.TempData (StrVal)
VALUES('test2')
-- #### データ確認
SELECT * FROM dbo.TempData
実行結果
では、identityが設定されている「Sn」カラムにデータを指定して登録してみましょう。
INSERT INTO dbo.TempData (Sn, StrVal)
VALUES(10, 'test10')
どうですか？
登録できないでしょう？？
下記のようなエラーメッセージが表示されると思います。
メッセージ 544、レベル 16、状態 1、行 1
IDENTITY_INSERT が OFF に設定されているときは、テーブル 'TempData' の ID 列に明示的な値を挿入できません。
そうなんです。
identityが設定されているカラムにはデータを指定して登録することが基本的にはできません。
では、どうすればできるのか！！！
SET IDENTITY_ISNERTを利用すればできます！！！
では、またテストをしながら使い方を理解しましょう～～
まず、INSERT句前に「IDENTITY_ISNERT」を「ON」に変更する必要があります。
SET IDENTITY_INSERT dbo.TempData ON
INSERT INTO dbo.TempData (Sn, StrVal)
VALUES(10, 'test10')
INSERT INTO dbo.TempData (Sn, StrVal)
VALUES(15, 'test15')
SELECT * FROM dbo.TempData
実行結果
では、ここでまたidentityを指定しないで登録してみましょう。
INSERT INTO dbo.TempData (StrVal)
VALUES('testvalue')
どうですか？
下記のようなエラーメッセージは表示されエラーになると思います。
メッセージ 545、レベル 16、状態 1、行 1
IDENTITY_INSERT が ON に設定されているか、レプリケーション ユーザーが NOT FOR REPLICATION ID 列に挿入しているときは、テーブル 'TempData' の ID 列には明示的な値を指定してください。
その理由は上記で「SET IDENTITY_INSERT dbo.TempData ON」を実行してまだ「OFF」にしてないからです。
では、identity_insertを「off」にしてからデータを登録してみましょう。
SET IDENTITY_INSERT dbo.TempData OFF
INSERT INTO dbo.TempData (StrVal)
VALUES('testvalue')
SELECT * FROM dbo.TempData
実行結果
正常に登録できました。
そしてidentityのデータは「16」として登録されています。
identity_insertを「off」するとidentityの最大値の次の番号から設定されます。
上記で手動で「10」と「15」を登録したので、「16」が登録されることになります。
再度「IDENTITY_INSERT」の使い方について簡単に説明すると！！
SET IDENTITY_INSERT [テーブル名] ON
上記のSQLを実行後データを登録すれば～～ O.K~~~
また説明が不足な部分がありましたら、下記のコメントにて教えてください。