returning an identity value when doing a single insert

Database
Teradata Employee

returning an identity value when doing a single insert

Hi,

I have a table with a field which is an identity column. Its the unique identifier of the record.

When a record is inserted via a stored proc, I want to return whatever that new id is to the calling proc.

Is there a function to do this?

Note: This stored proc could be called by many parallel jobs so potentially could be called x2 at the same time. Select Max(column) therefore can potentially get it wrong.. Thats why I was wondering if there was a returning function is something...

Thanks
2 REPLIES
Highlighted
Enthusiast

Re: returning an identity value when doing a single insert

Some thoughts....

The feature you are looking for is supported in latest JDBC driver APIs (yeah, it doesn't help you :-) )

Relying on the identity column values to reflect the chronological order in which records were inserted into the table is generally not a good idea. (ie the MAX() stuff might give surprises). especially if you have bulk inserts going on ...

So if you have only single record inserts (which I presume is the case from your description)

you can have a transaction defined in your SP

BT;
LOCKING TABLE mytable FOR WRITE
INSERT the record here ...
SELECT the max(id) here
ET;

The explicit WRITE lock will save you any dead lock situation.

Also create a value ordered NUSI on the id column, that will make retrieving the max(id) faster ...

Or may be you need not have it to be an identity column (that would have been my choice), you can keep it as a normal integer column and create an NUSI as before, and do this in your SP

BT;
LOCKING TABLE mytable FOR WRITE SELECT COALESCE(MAX(ID), 0)+1 INTO :ID FROM mytable;
INSERT INTO mytable VALUES(:ID, ...... );
ET;

might save a lot of troubles with "identity" :-)
Enthusiast

Re: returning an identity value when doing a single insert

From personal experience I have had loads of trouble with IDENTITY fields in Teradata because they are not chronolgical but based on the AMP the data is stored on (I think Im right in saying that)

We do bulk Inserts of data and found that using the
row_number() function to be very useful. So you 'll always have your 1,2,3,4,5,etc etc....