Can I select the identity value from an insert statement?

352455 pts.
Tags:
IBM DB2
IBM iSeries
INSERT statement
Our company is using iSeries for DB2/AS400 and we're hoping someone can help us with this simple question. Would there be a way to return the identity value from an insert statement without using two lines of SQL? We're currently being forced to use inline SQL in C# to perform an insert. Basically, we need the DB2 equivalent of Oracle's "RETURNING".
INSERT INTO AwesomeTable (column1, column2, etc.)
    VALUES (value1, value2, etc.)
    RETURNING something;
Thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta

    The requirements aren't totally clear. Neither the Oracle nor DB2 versions are given. Until recently, Oracle didn't have a SQL-standard IDENTITY feature, and it's not exactly SQL-standard in how it "returns" its value. In reasonably current DB2, there is no need to use any kind of RETURNING clause.

    But it's also not clear why it makes a difference if one or two or many statements must be used to access the latest IDENTITY value. That almost makes the question seem like an academic question. Can you give a business reason for the requirement? It shouldn't matter as long as you use the same connection.

    The last IDENTITY value should be available as long as the connection is continuous. E.g., until the transaction is COMMITted and as long as the connection continues, you should be able to access the value.

    Tom

    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following