Can I select the identity value from an insert statement?
Tags:
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!
Asked:
Last updated:
Discuss This Question: 1 Reply
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