Obtain id of an insert in the same statement

Question

Hello

Is there any way of insert a row in a table and get the new generated ID, in only one statement? I want to use JDBC, and the ID will be generated by a sequence or will be an autoincrement field.

Thanks for your help.

John Pollancre

Answer 1

The value of the auto-generated ID is not known until after the INSERT is executed, because other statements could be executing concurrently and the RDBMS gets to decide how to schedule which one goes first.

Any function you call in an expression in the INSERT statement would have to be evaluated before the new row is inserted, and therefore it can't know what ID value is generated.

I can think of two options that are close to what you're asking:

• Write a trigger that runs AFTER INSERT, so you have access to the generated ID key value.

• Write a procedure to wrap the insert, so you can execute other code in the procedure and query the last generated ID.

However, I suspect what you're really asking is whether you can query for the last generated ID value by your current session even if other sessions are also inserting rows and generating their own ID values. You can be assured that every RDBMS that offers an auto-increment facility offers a way to query this value, and it tells you the last ID generated in your current session scope. This is not affected by inserts done in other sessions.

Answer 2

using getGeneratedKeys():

resultSet = pstmt.getGeneratedKeys(); 

if (resultSet != null && resultSet.next()) { 
    lastId = resultSet.getInt(1); 
}

Answer 3

The id generated by a sequence can be obtained via

insert into table values (sequence.NextVal, otherval)
select sequence.CurrVal

ran in the same transaction as to get a consistent view.

Answer 4

You can use the RETURNING clause to get the value of any column you have updated or inserted into. It works with trigger (i-e: you get the values actually inserted after the execution of triggers). Consider:

SQL> CREATE TABLE a (ID NUMBER PRIMARY KEY);

Table created
SQL> CREATE SEQUENCE a_seq;

Sequence created
SQL> VARIABLE x NUMBER;
SQL> BEGIN
  2     INSERT INTO a VALUES (a_seq.nextval) RETURNING ID INTO :x;
  3  END;
  4  /

PL/SQL procedure successfully completed
x
---------
1

SQL> /

PL/SQL procedure successfully completed
x
---------
2

Answer 5

I think you'll find this helpful:

I have a table with a auto-incrementing id. From time to time I want to insert rows to this table, but want to be able to know what the pk of the newly inserted row is.

String query = "BEGIN INSERT INTO movement (doc_number) VALUES ('abc') RETURNING id INTO ?; END;";
OracleCallableStatement cs = (OracleCallableStatement) conn.prepareCall(query);
cs.registerOutParameter(1, OracleTypes.NUMBER );
cs.execute();
System.out.println(cs.getInt(1));

Source: Thread: Oracle / JDBC Error when Returning values from an Insert

Answer 6

I couldn't comment, otherwise I would have just added to dfa's post, but the following is an example of this functionality with straight JDBC.

http://www.ibm.com/developerworks/java/library/j-jdbcnew/

However, if you are using something such as Spring, they will mask a lot of the gory details for you. If that can be of any assistance, just good Spring Chapter 11, which is the JDBC details. Using it has saved me a lot of headaches.

Answer 7

I couldn't comment otherwise I would have added to Vinko Vrsalovic's post:

The id generated by a sequence can be obtained via 

insert into table values (sequence.NextVal, otherval)
select sequence.CurrVal

ran in the same transaction as to get a consistent view.

Updating de sequence after getting a nextval from it is an autonomous transaction. Otherwise another session would get the same value from the sequence. So getting currval will not get the inserted id if anothers sesssion has selected from the sequence in between the insert and select.

Regards, Rob