• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Using select values for inserts in a batch

 
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You could just put the sequence select into your insert:
 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic