「SQL Serverの連番フィールドに値をセットする」の版間の差分

2010年10月23日 (土) 17:18時点における版

自動連番採用機能

• あるカラムに値をセットしなくても自動的に連続した数値がセットされる機能は大抵のDBMSが持っています。
  • oracleならSEQUENCE、mysqlならAUTO_INCREMENT、postgresqlならserial型(=sequence)、そして、SQL ServerならIDENTITY。
  • ちなみにMS-Accessではオートナンバー型と呼んでいました。

データをセットしたい

• システムが完成して、運用し始めたら勝手に連続した番号が振られて問題ないのですが、開発期間中や、初期データを投入する場合はこの列に特定の値をセットしたいことがあります。
• そんなときに調べたことをメモしておきます。

作業ログ

IDENTITYプロパティを設定した列(以下ID列)を持つテーブルを作成

CREATE TABLE hoge_table (
 id int NOT NULL IDENTITY (1, 1),
 onamae varchar(40) NULL
);
コマンドは正常に完了しました。

ID列に値を指定してINSERTを実行

INSERT INTO hoge_table values (1000, 'akiko');
メッセージ 8101、レベル 16、状態 1、行 1
列リストが使用されていて、IDENTITY_INSERT が ON のときに限り、テーブル 'hoge_table' の ID 列に明示的な値を指定できます。

ID列に値を指定してINSERTを実行

• 列リストが指定されていないことがエラー原因の一つのようなので、列リストを明示してみる。

INSERT INTO hoge_table (id, onamae) values (1000, 'akiko');
メッセージ 544、レベル 16、状態 1、行 1};
IDENTITY_INSERT が OFF に設定されているときは、テーブル 'hoge_table' の ID 列に明示的な値を挿入できません。

IDENTITY_INSERTプロパティをセットしてINSERT

SET IDENTITY_INSERT hoge_table ON
INSERT INTO hoge_table (id, onamae) values (1000, 'akiko');
(1 行処理されました)

データを確認

• ID列に指定値がセットされていることを確認。

select * from hoge_table
id          onamae
----------- ----------------------------------------
1000        akiko
(1 行処理されました)

ID列に値を指定せずにINSERT

SET IDENTITY_INSERT hoge_table OFF
INSERT INTO hoge_table (onamae) values ('akiko');
(1 行処理されました)

データ確認

• 直前のID列の値“1000”に続く“1001”が採用されることを確認。

select * from hoge_table
id          onamae
----------- ----------------------------------------
1000        akiko
1001        akiko
(2 行処理されました)