I see this question everywhere. I sure hope there is an answer.
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 :)
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?
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.
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
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.
If you write an application you might utilize it, I didn't test if this is also possible in a Stored Procedure.