23

I want to get the value of the last ID insert in a table. How I can do this?

Paolo Forgia
  • 6,572
  • 8
  • 46
  • 58
Enrique San Martín
  • 2,202
  • 7
  • 30
  • 51

4 Answers4

29

Well the solution that I use is:

select id from NEW TABLE (insert into (val1, val2, ...) values ('lorem', 'ipsum', ...))

This gets the id column from the last row inserted in the DB :)

Mookie Wilson
  • 188
  • 2
  • 10
Enrique San Martín
  • 2,202
  • 7
  • 30
  • 51
  • 2
    For more information on this approach, see documentation on the [SELECT from INSERT statement](http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.relg%2Finsl.htm) – Steven Jun 22 '12 at 19:41
  • 1
    Tragedy. That "SELECT from INSERT" documentation either changed locations, or became unavailable. – starlocke Aug 01 '14 at 13:20
  • 3
    Check section *Selecting inserted values* in your DB2 SQL Programming Guide. An example from my guide is `SELECT EMPNO, HIRETYPE, HIREDATE FROM FINAL TABLE ( INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL) VALUES('Mary Smith', 35000.00, 11, 'Associate'))` – Telemat Feb 07 '15 at 17:09
  • 4
    [Selecting values while inserting data](https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.apsg/src/tpc/db2z_selectvalueinsert.dita) – MyPasswordIsLasercats Mar 22 '16 at 12:53
16
SELECT IDENTITY_VAL_LOCAL() AS VAL FROM SYSIBM.SYSDUMMY1

See docs.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    This one worked for me but I needed to use OpenQuery to get it to work as we have the DB2 set up as a linked server. Hope that may help someone else – Brian Davis Sep 17 '20 at 19:47
0

Have a look at this answer.

http://www.sitepoint.com/php-database-db2/

// get the last inserted ID into the specified table  
// int lastInsertID(string $tblName)  
function lastInsertID($tblName)  
{  
 if ($this->transIsOpen())  
 {  
   $sql = "SELECT SYSIBM.IDENTITY_VAL_LOCAL() AS id FROM " . $tblName;  
   $rs = $this->query($sql);  
   return $this->fetch($rs, "id");  
 }  
 return -1;  
}

OR this

http://www.php.net/manual/en/function.db2-last-insert-id.php#98361

Vibhaj
  • 189
  • 6
-3
int keyId = -1;
preparedStatement.executeUpdate();
resultSet = preparedStatement.getGeneratedKeys();
if (resultSet.next()) {
    keyId = rs.getInt(1);
}

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()

Update: and don't forget to create preparedStatement with the following flag Statement.RETURN_GENERATED_KEYS otherwise it won't work)))

Anatoly
  • 5,056
  • 9
  • 62
  • 136