• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using select values for inserts in a batch

 
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic