All Forums Database
coffee 3 posts Joined 07/12
05 Sep 2012
How do I retrieve the most recent used autonumber??

I see this question everywhere.  I sure hope there is an answer.

 

Use case:

Master table that has a column (id) with "generated always as identity..." so it will create the unique id.

Other tables that have a foreign key (id_fk).

 

1. Insert row into master table.

2. Retrieve the unique id that was just auto created in the master table.

3. insert rows into the other tables using the value of id in the id_fk column.

 

How do I do number 2?  (No wise cracks :)

 

coffee 3 posts Joined 07/12
06 Sep 2012

Really?  Noone knows?  I'm in the process of migrating xsl generated inserts from a sql server system to Teradata.   I'm new to Teradata sql.  In sql server @@identity returns the just created auto id so it can be used as a foreign key for other inserts.  How is this accomplished in Teradata?

ulrich 816 posts Joined 09/09
07 Sep 2012

Teradata is a MPP system, so data is distributed and processed in parallel. So this is implemented differently-

In easy words: To become not sequencial the identity process is done Vproc specific and each vproc is requesting a number range. So you will not get a closed number range if you insert data to a table.

Something like

1. insert creates ID 1001

2. insert creates ID 2001

3. insert creates ID 2002

4. insert creates ID 1

5. insert creates ID 1002

so not a single system variable can hold the latest id or you would break the parallelism.

Check also the manuals and there exists a lot of posts here or in www.teradataforum.com

Do you do bulk inserts or sequential?

Consider to do the id generation with SQL and maintain a last_id in a generic reference table

 

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

AndreyS 2 posts Joined 08/14
18 Aug 2014

Hello, please advise any practice sequential insertion. Thank you.

dnoeth 4628 posts Joined 11/04
18 Aug 2014

In ODBC/JDBC/CLI/.NET support a feature called "Auto-generated key retrieval" where the new identity value is returned as an answer set to the client, e.g.
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/index.html#page/Connectivity/B035_2509_082K/2509ch08.10.25.html
 
If you write an application you might utilize it, I didn't test if this is also possible in a Stored Procedure.

Dieter

You must sign in to leave a comment.