Using select values for inserts in a batch

[mohamed zafer]

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]

You could just put the sequence select into your insert:

INSERT INTO AID_N VALUES ((ID_SEQ.nextval), "1");

[Gagan Gulati]

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]

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.