GigasMegas

photo by BaLLYoOo

SQL SERVER 2008 のテーブルにIDENTITY列を追加して、データ挿入時にオートインクリメントでID値を自動増加させることはよくあります。

しかし、一旦、IDENTITY列を設定してあるテーブルにID値をあらかじめ明示して挿入しようとすると、IDENTITY列が設定してあるので、追加できません。

これは、これで正しい仕様だと思います。

ですが、テーブル定義した直後にあらかじめデータをいくつか挿入しておきたいことがあります。

こんな時、どうすればいいのでしょうか？

SET IDENTITY_INSERTを”ON”、”OFF”する

このような時のために、IDENTITY列に値を挿入できる機能が備わっています。

構文

SET IDENTITY_INSERT [ データベース名. [ schema_name ] . ] テーブル名 { ON | OFF }

引数

データベース名

指定したテーブルが含まれているデータベースの名前を指定します。

schema_name

テーブルが所属するスキーマの名前を指定します。

テーブル名

ID 列があるテーブルの名前を指定します。

Dim objConn = New SqlConnection("Data Source=" DataSource ";Initial Catalog="InitialCatalog  ";Integrated Security=" My.Settings.DBIntegratedSecurity)
Dim objCmd = New SqlCommand
Dim SQLStr As String

SQLStr = "SET IDENTITY_INSERT combo ON"
objCmd = New SqlCommand(SQLStr, objConn)
objCmd.CommandType = CommandType.Text
objCmd.ExecuteNonQuery()

'ここでINSERT処理をする

'INSERT処理が終わったら
'SET IDENTITY_INSERTを”OFF”に戻す
SQLStr = "SET IDENTITY_INSERT combo OFF"
objCmd = New SqlCommand(SQLStr, objConn)
objCmd.CommandType = CommandType.Text
objCmd.ExecuteNonQuery()

制限 ｰ SET IDENTITY_INSERT

IDENTITY_INSERT プロパティを ON に設定できるのは、セッション内の 1 つのテーブルのみ。

1 つのテーブルで既にこのプロパティが ON に設定されている場合、エラーが返される。

ID値の仕様

挿入する値がテーブルの現在の ID 値よりも大きい場合、新しく挿入された値が現在の ID 値として自動的に使用されます。

例：それまでID値のMAXが”10″で、そこにID値”100″を挿入すると、次のID値は”11″ではなく、”101″となります。

その他

SET IDENTITY_INSERTを使用すれば、テーブル定義後のデータの事前挿入、DELETEステートメントによって発生する、ID値のギャップ調整など、いろいろ使用できそうな場面がありそうです。

関連するエントリー

None Found

SQL Server, データベース 2008, id, identity, sql, サーバー, 設定