Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012

    Smile Unanswered: Selecting the recently added value of identity column

    Here's the problem.
    Household table has foreign key columns userId, addressId and documentId
    So I need to first insert data into these three tables.
    After inserting the data, I've to select userId, addressId, and documentId from these tables. (These are identity columns and will always use DEFAULT to insert value).
    Now finally I have values of all the three columns and can perform insert query on Household table.
    I can implement it in java or other programming language, but this process will be slow as it involves need to create connection multiple times with database.

    Isn't there's anyway I can perform this task in sql and doesn't require programming language.

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Quote Originally Posted by harish.8988 View Post
    it involves need to create connection multiple times with database.
    Not sure I understand why you need multiple connections.

    You can use the IDENTITY_VAL_LOCAL() function to retrieve the one most recently assigned identity value. I think you might also be able to use intermediate result tables, like "SELECT addressid FROM FINAL TABLE (INSERT INTO address ...)"

  3. #3
    Join Date
    May 2009
    Provided Answers: 1
    This might depend on the version of DB2 you are using but another method is to use the SELECT FROM result-set. See Retrieval of result sets from an SQL data change statement - IBM DB2 9.7 for Linux, UNIX, and Windows for more details.

    For example, assuming a 3 column table of identity-col1, col2, col3:
    SELECT identity-col1
        INSERT INTO table-name
        (col2, col3)
    This will perform an Insert and immediately select the value that was generated for that row from the result.

  4. #4
    Join Date
    Mar 2012
    Thanks, Its working

    One more question, what it will return if Insert command failed to complete successfully.

  5. #5
    Join Date
    Jan 2007
    Jena, Germany
    If the INSERT failed, you get a negative SQLCODE and no result set.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts