Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Using select values for inserts in a batch  RSS feed

 
mohamed zafer
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi There,

I want to insert into mutliple tables using batch update like this,

Statement stmt= dbCon.createStatement();
stmt.addBatch("SELECT ID_SEQ.nextval as AID from dual");
stmt.addBatch("INSERT INTO AID_N VALUES (<AID>, "1");
stmt.addBatch("INSERT INTO AID_B VALUES (<AID>, "43");
stmt.addBatch("INSERT INTO AID_C VALUES (<AID>, "2");

How can I use the value AID selected in the first query, in the subsequent queries [inplace of <AID>].

Thanks
Mohamed Zafer
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could just put the sequence select into your insert:
 
Gagan Gulati
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not really.
Because if you do that, 3 nextvals would come from the sequence.

Instead you could get the result of the select query first and then add the three Inserts in the batch
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was assuming <AID> was the PK (which is the normal reason for using sequences). Reading the question again, I see that perhaps this isn't how mohamed zafer want's to use it. In which case you are correct, you need to do it in two seperate Statements.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!