Using insert in a select statement in SQL

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开发者_JS百科 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?

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

You can use the RETURNING keyword in your INSERT statement.

INSERT INTO Person (...) VALUES (...)
RETURNING person_id INTO nbr_id

Have you tried the OUTPUT command?

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

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