トップ回答者
SQLでINSERTした後にそのIDを得るには?

質問
-
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()しています。
これで問題はないのですが、もし複数のコマンドを一度に実行できればよりスマートなのかな?とも思うのですが、そのような実行方法はあるのでしょうか?
回答
-
SurferOnWwwさんがコメントされているように ; で区切って一度に連続して実行させるべきです。また@@IDENTITYの説明を読めばSCOPED_IDENTITY()を使うべきとわかるはずですが。ちなみにExecuteScalarにはそのままズバリの例が載っています。
INSERT INTO A (Name, Age) OUTPUT INSERTED.ID VALUES (@Name, @Age)
呼び出し元はこのINSERT文に対して値を読み出すことができます。(ExecuteNonQueryではなくExecuteReaderやExecuteScalar)
- 回答としてマーク あくあれっど 2012年9月9日 12:17
-
回答は既にお二人が書かれている通りですが、少し気になったところがありましたので補足しておきます。
また、上記のINSERTコマンド、SELECTコマンドをそれぞれを実行するときに、Connectionをその都度Open()して実行後Close()しています。
これで問題はないのですが、もし複数のコマンドを一度に実行できればよりスマートなのかな?とも思うのですが、そのような実行方法はあるのでしょうか?
問題ありです。Connectionをその都度Open()、Close()してはいけません。INSERTからSelectの間に他の誰かがInsertすれば、誤ったIDを取得する可能性があります。トランザクションや分離レベルなど、複数のアクセスが同時に発生する場合の知識が不足しているように思います。SQL Serverのバッチという概念をご存じなくても、トランザクションによる処理を思い付かなければなりません。
今回、SCOPE_IDENTITYを使うのはもちろんですが、バッチで処理する意味を正しく捉えて下さい。バッチは、単に連続してSQL文を実行するための便利なものだけではありません。佐祐理さんが紹介されているSCOPE_IDENTITYのページに書かれているスコープの意味を知ることが大事です。
また、トランザクションの知識は、将来、IDではないユニークな番号(例えば日付毎に1から連番になっている番号)を採番することがあれば、役に立つでしょう。IDは、自動で採番された番号をSCOPE_IDENTITYなどによって取得できるように特別に用意された便利なものですが、トランザクションを知っていれば、そのような仕組みを自分で実現することも難しいことではありません。
★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
- 回答としてマーク あくあれっど 2012年9月9日 12:18
すべての返信
-
SurferOnWwwさんがコメントされているように ; で区切って一度に連続して実行させるべきです。また@@IDENTITYの説明を読めばSCOPED_IDENTITY()を使うべきとわかるはずですが。ちなみにExecuteScalarにはそのままズバリの例が載っています。
INSERT INTO A (Name, Age) OUTPUT INSERTED.ID VALUES (@Name, @Age)
呼び出し元はこのINSERT文に対して値を読み出すことができます。(ExecuteNonQueryではなくExecuteReaderやExecuteScalar)
- 回答としてマーク あくあれっど 2012年9月9日 12:17
-
回答は既にお二人が書かれている通りですが、少し気になったところがありましたので補足しておきます。
また、上記のINSERTコマンド、SELECTコマンドをそれぞれを実行するときに、Connectionをその都度Open()して実行後Close()しています。
これで問題はないのですが、もし複数のコマンドを一度に実行できればよりスマートなのかな?とも思うのですが、そのような実行方法はあるのでしょうか?
問題ありです。Connectionをその都度Open()、Close()してはいけません。INSERTからSelectの間に他の誰かがInsertすれば、誤ったIDを取得する可能性があります。トランザクションや分離レベルなど、複数のアクセスが同時に発生する場合の知識が不足しているように思います。SQL Serverのバッチという概念をご存じなくても、トランザクションによる処理を思い付かなければなりません。
今回、SCOPE_IDENTITYを使うのはもちろんですが、バッチで処理する意味を正しく捉えて下さい。バッチは、単に連続してSQL文を実行するための便利なものだけではありません。佐祐理さんが紹介されているSCOPE_IDENTITYのページに書かれているスコープの意味を知ることが大事です。
また、トランザクションの知識は、将来、IDではないユニークな番号(例えば日付毎に1から連番になっている番号)を採番することがあれば、役に立つでしょう。IDは、自動で採番された番号をSCOPE_IDENTITYなどによって取得できるように特別に用意された便利なものですが、トランザクションを知っていれば、そのような仕組みを自分で実現することも難しいことではありません。
★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
- 回答としてマーク あくあれっど 2012年9月9日 12:18