MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » November 2012

Re: SQL how to get Generated Always value on Insert



fixed

On 09 Nov 2012 10:14, Scott Mildenberger wrote:
I have a auto-generated field in a table defined as

ARCHIVENUM INTEGER GENERATED ALWAYS AS IDENTITY
primary key NOT NULL,

When I do an Insert on the table is there a way to get the new
generated value without having to do a Select on that record?


_i IDENTITY_VAL_LOCAL i_
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzscaidentity.htm
"IDENTITY_VAL_LOCAL is a non-deterministic function that returns the most recently assigned value for an identity column.

>>-IDENTITY_VAL_LOCAL--(--)-----------><

The function has no input parameters. The result is a DECIMAL(31,0) regardless of the actual data type of the identity column that the result value corresponds to.

The value returned is the value that was assigned to the identity column of the table identified in the most recent insert operation (specified in either an INSERT statement or a MERGE statement) ...
"

_i Selecting inserted values i_ [aka SELECT FROM INSERT]
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafysfins.htm
"You can retrieve the values for rows that are being inserted by specifying the INSERT statement in the FROM clause of a SELECT statement.

When you insert one or more rows into a table, you can select the result rows of the insert operation. These rows include any of the following values:

* The value of any generated column, such as identity, ROWID, or row change timestamp columns
* Any default values used for columns
* All values for all rows inserted by a multiple-row insert operation
* Values that were changed by a before insert trigger
...
"






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact