0

I have a table person with columns personID, firstName, lastName, DOB and sex. As I insert each record I auto increment the personID column with a sequence. I need that value for each record I insert as I have to send it in another query. Is there any way I can use select statement in an insert statement to retrieve the value. I cannot use 'where' because I accept duplicates of other columns. So, each unique person is identified by the personID only. I'm using jdbc API to connect to DB. Is there any possibility of doing the call in JDBC?

Srinivas
  • 545
  • 5
  • 9
  • 16

4 Answers4

2

Not sure if this helps, but if you're using PL/SQL you can use the RETURNING INTO clause...

For example:

DECLARE
 x emp.empno%TYPE;
BEGIN
  INSERT INTO emp
  (empno, ename)
  VALUES
  (seq_emp.NEXTVAL, 'Morgan')
  RETURNING empno
  INTO x;

  dbms_output.put_line(x);
END;
/

Ref: http://psoug.org/reference/insert.html

cagcowboy
  • 30,012
  • 11
  • 69
  • 93
  • I'm actually using jdbc. Is there any possibility of doing it in JDBC? – Srinivas Sep 19 '11 at 19:37
  • 1
    yes there is and the accepted answer in the following post does exactly that: http://stackoverflow.com/questions/1976625/value-from-last-inserted-row-in-db –  Sep 19 '11 at 20:57
1

You can use the RETURNING keyword in your INSERT statement.

INSERT INTO Person (...) VALUES (...)
RETURNING person_id INTO nbr_id
Tom H
  • 46,766
  • 14
  • 87
  • 128
0

I think NEXTVAL and CURRVAL should be of assistance. Check this reference: Sequence Pseudocolumns, the last example (Reusing the current value of a sequence)

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
0

Have you tried the OUTPUT command?

INSERT INTO TableName(FirstName, LastName, DOB, Sex)
VALUES (<FirstNamei>, <LastNamei>, <DOBi>, <Sexi>)
OUTPUT inserted.PersonID
WHERE <Conditions>
Lonsor
  • 9
  • 2