How do I retrieve the most recent used autonumber??

Database
Fan

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 :)

4 REPLIES
Fan

Re: How do I retrieve the most recent used autonumber??

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?

Supporter

Re: How do I retrieve the most recent used autonumber??

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

Re: How do I retrieve the most recent used autonumber??

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

Junior Contributor

Re: How do I retrieve the most recent used autonumber??

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/2509c...

If you write an application you might utilize it, I didn't test if this is also possible in a Stored Procedure.