【SQLServer】「列リストが使用されていて、IDENTITY_INSERTが ON のときだけ」が出たときの対策法

久しぶりにIT小ネタを書きます。

SQLServerを利用していて、テーブルからテーブルへのコピー(Insert)が出来なかったので原因を調べていたことろ、列を明示的に列挙しておかないといけないということがわかりました。

答えは簡単な事でしたが、エラーの内容と対策をまとめましたのでご紹介します。

エラーとなったSQL

【開発環境】

開発PC:Windows10 64bit

サーバー:Windows Server 2012 R2 Standard 64bit

データベース:SQLServer 2016 Standard

 

testとhonのスキーマ(同じ名前のテーブル)があり、hon→testへinsert文でコピーを行いたい

 

・エラーメッセージ
列リストが使用されていて、IDENTITY_INSERTが ON のときだけ、テーブル ‘xxx’ の ID 列に明示的な値を指定できます。

 

普段本番環境のマスターをテスト環境にコピーしたりするときにはあまり気にしていなかったが、テーブル内に自動採番するフィールドなどがある場合は
IDENTITY_INSERTプロパティをONにして、値を明示的に記述してやる必要がある。

 

例としては、こんな感じの列に「IDENTITY」プロパティを設定したテーブルがある場合。

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

コードサンプル

対策としてはこのようなSQL文を書く。

 

SET IDENTITY_INSERT [テーブル名] ON;

INSERT INTO [テーブル名](ID,Name,CreateTime)
SELECT ID,Name,CreateTime
FROM [テーブル名]

SET IDENTITY_INSERT [テーブル名] OFF

 

ID,Name,CreateTimeの所が列にあたる

SET IDENTITY_INSERTがONの後はOFFに戻しておく。

これで明示的に値を設定して INSERT できるようになる。

 

※IDENTITY を設定した列は自動で 1 から値が割り当てられるが、SET IDENTITY_INSERTをONにして明示的に値を設定した場合、次に割り当てられる値はテーブルに追加された値の中で最も大きな値の次の番号から割り当てられます。新規にレコードを挿入しても値が重複することはありません。

参考サイト:http://msdn.microsoft.com/ja-jp/library/ms188059.aspx
IDENTITY_INSERT プロパティを ON に設定できるのは、セッション内の 1 つのテーブルのみとなっている。

 

 

ANAマイルの貯め方

マイルが貯まるクレジットカードだけでは大量のANAマイルは貯まりません。
では、どうすれば貯まるのか?ANAマイルを大量に貯めたい人は読んで下さい。
最新情報をチェックしよう!
PAGE TOP