SQLでINSERTした後にそのIDを得るには？

Question

SQL Server 2008を利用した開発をしています。

AというテーブルにIDというIdentityを有効にした項目を定義しています。

追加後に、それに自動的に割り当てられるIDを取得したいのですが、どのようにしたらよりスマートに実行できますか？

今は次のようにして実現しています。

"INSERT INTO A (Name, Age) VALUES (@Name, @Age)"

を実行後、

"SELECT @@IDENTITY"

を実行して最後のIDを得ることで追加したIDを取得しています。

ただ、こうするとアクセスが集中したときにほぼ同時に他のINSERTが実行されてしまい、そのIDを返さないか心配です。あまりあり得ないとは思いますが、絶対あり得ないとはいえないので。

また、上記のINSERTコマンド、SELECTコマンドをそれぞれを実行するときに、Connectionをその都度Open()して実行後Close()しています。

これで問題はないのですが、もし複数のコマンドを一度に実行できればよりスマートなのかな？とも思うのですが、そのような実行方法はあるのでしょうか？

Answer 1

 何故単一バッチで INSERT と SELECT を実行（; で区切って続ける）しないのですか？

Answer 2

SurferOnWwwさんがコメントされているように ; で区切って一度に連続して実行させるべきです。また@@IDENTITYの説明を読めばSCOPED_IDENTITY()を使うべきとわかるはずですが。ちなみにExecuteScalarにはそのままズバリの例が載っています。

別の方法としてINSERT文にはOUTPUT句が使えます。

INSERT INTO A (Name, Age) OUTPUT INSERTED.ID VALUES (@Name, @Age)

呼び出し元はこのINSERT文に対して値を読み出すことができます。（ExecuteNonQueryではなくExecuteReaderやExecuteScalar）

Answer 3

回答は既にお二人が書かれている通りですが、少し気になったところがありましたので補足しておきます。

また、上記のINSERTコマンド、SELECTコマンドをそれぞれを実行するときに、Connectionをその都度Open()して実行後Close()しています。

これで問題はないのですが、もし複数のコマンドを一度に実行できればよりスマートなのかな？とも思うのですが、そのような実行方法はあるのでしょうか？

問題ありです。Connectionをその都度Open()、Close()してはいけません。INSERTからSelectの間に他の誰かがInsertすれば、誤ったIDを取得する可能性があります。トランザクションや分離レベルなど、複数のアクセスが同時に発生する場合の知識が不足しているように思います。SQL Serverのバッチという概念をご存じなくても、トランザクションによる処理を思い付かなければなりません。

今回、SCOPE_IDENTITYを使うのはもちろんですが、バッチで処理する意味を正しく捉えて下さい。バッチは、単に連続してSQL文を実行するための便利なものだけではありません。佐祐理さんが紹介されているSCOPE_IDENTITYのページに書かれているスコープの意味を知ることが大事です。

また、トランザクションの知識は、将来、IDではないユニークな番号（例えば日付毎に１から連番になっている番号）を採番することがあれば、役に立つでしょう。IDは、自動で採番された番号をSCOPE_IDENTITYなどによって取得できるように特別に用意された便利なものですが、トランザクションを知っていれば、そのような仕組みを自分で実現することも難しいことではありません。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 4

ありがとうございます。勉強になります。

Answer 5

なるほど。SCOPED_IDENTITY()を使うべきなんですね。リンク先を呼んで勉強したいと思います。

ExecuteScalarの例をみて実装していきたいと思います。

またOUTPUTによる実装の方法も教えて頂きありがとうございました。

Answer 6

勉強不足でした。丁寧な回答有り難うございます。

スコープやトランザクションなど勉強したいと思います。

ありがとうございました。