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?
Add a comment
|
4 Answers
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;
/
2 Comments
Srinivas
I'm actually using jdbc. Is there any possibility of doing it in JDBC?
yes there is and the accepted answer in the following post does exactly that: stackoverflow.com/questions/1976625/…
You can use the RETURNING keyword in your INSERT statement.
INSERT INTO Person (...) VALUES (...)
RETURNING person_id INTO nbr_id
Comments
Have you tried the OUTPUT command?
INSERT INTO TableName(FirstName, LastName, DOB, Sex)
VALUES (<FirstNamei>, <LastNamei>, <DOBi>, <Sexi>)
OUTPUT inserted.PersonID
WHERE <Conditions>
Comments
I think NEXTVAL and CURRVAL should be of assistance. Check this reference: Sequence Pseudocolumns, the last example (Reusing the current value of a sequence)
