CODEWITHSUNDEEP
sqloracle-databasejdbc
Srinivas
Srinivas

Reputation: 565

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 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?

Upvotes: 0

Views: 295

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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

Upvotes: 0

Lonsor
Lonsor

Reputation: 9

Have you tried the OUTPUT command?

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

Upvotes: 0

Tom H
Tom H

Reputation: 47464

You can use the RETURNING keyword in your INSERT statement.

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

Upvotes: 1

cagcowboy
cagcowboy

Reputation: 30848

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

Upvotes: 2

Related Questions