SQL Serverの連番フィールドに値をセットする 2010年10月23日 (土) 17:02時点におけるDondariadmin (トーク | 投稿記録)による版 (ページの作成: == 自動連番採用機能 == *あるカラムに値をセットしなくても自動的に連続した数値がセットされる機能は大抵のDBMSが持っています...) (差分) ← 古い版 | 最新版 (差分) | 新しい版 → (差分)

自動連番採用機能

• あるカラムに値をセットしなくても自動的に連続した数値がセットされる機能は大抵の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

);
&color(blue){コマンドは正常に完了しました。};
}}

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

INSERT INTO hoge_table values (1000, 'akiko');

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

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

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

INSERT INTO hoge_table (id, onamae) values (1000, 'akiko');

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

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

SET IDENTITY_INSERT hoge_table ON
INSERT INTO hoge_table (id, onamae) values (1000, 'akiko');

&color(blue){(1 行処理されました)};
}}

データを確認

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

select * from hoge_table
&color(blue){id onamae};
&color(blue){----------- ----------------------------------------};
&color(blue){1000 akiko};
&color(blue){(1 行処理されました)};
}}

ID列に値を指定せずにINSERT

SET IDENTITY_INSERT hoge_table OFF
INSERT INTO hoge_table (onamae) values ('akiko');

&color(blue){(1 行処理されました)};
}}

データ確認

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

select * from hoge_table
&color(blue){id onamae};
&color(blue){----------- ----------------------------------------};
&color(blue){1000 akiko};
&color(blue){1001 akiko};
&color(blue){(2 行処理されました)};
}}

Tweet