Teradata - Auto Generated Key retrieval feature

Auto Generated Key retrieval:

In all earlier examples there is no way to determine the value assigned to the identity column for the inserted row in the table.

The user would have to query the table to find that out.

However with the Auto-generated Key retrieval feature we would get to know the assigned identity values.

To enable this feature we use the command as follows from BTEQ.

.[SET] AUTOKEYRETRIEVE [OFF|COLUMN|ROW]

Where:

• OFF = Disabled. Same that earlier

• COLUMN = Will display only the IDCol.

• ROW = Enabled, will display entire row

Note that This is a BTEQ command and hence we cannot run this through SQL assistant.

Example:

Assume a table as shown below :

CREATE SET TABLE EDW_RESTORE_TABLES.EMPLOYEE4 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      EMPLOYEEID INTEGER GENERATED ALWAYS AS IDENTITY

           (START WITH 1

            INCREMENT BY 1

            MINVALUE 1

            MAXVALUE 4

            CYCLE),

      EMPLNAME CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( EMPLOYEEID );

/* Next is a simple insert. BY default the AUTOKEYRETRIEVE feature is OFF and hence it does not return back the identity column value */

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO EMPLOYEE4 VALUES(,'sukul');

INSERT INTO EMPLOYEE4 VALUES(,'sukul');

 *** Insert completed. One row added.

 *** Total elapsed time was 1 second.

/* Next we turn on the AUTORETRIEVE COLUMN feature*/

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

.SET AUTOKEYRETRIEVE COLUMN;

.SET AUTOKEYRETRIEVE COLUMN;

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO EMPLOYEE4 VALUES(NULL,'shushant');

INSERT INTO EMPLOYEE4 VALUES(NULL,'shushant');

 *** Insert completed. One row added.

 *** Total elapsed time was 1 second.

 EMPLOYEEID

-----------

          2

/* As shown above because the AUTORETRIEVE COLUMN feature was turned on only the value of identity column was returned */

/* Next we turn on the AUTORETRIEVE ROW feature*/

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

.SET AUTOKEYRETRIEVE ROW;

.SET AUTOKEYRETRIEVE ROW;

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO EMPLOYEE4 VALUES(,'bhanu');

INSERT INTO EMPLOYEE4 VALUES(,'bhanu');

 *** Insert completed. One row added.

 *** Total elapsed time was 1 second.

 EMPLOYEEID  EMPLNAME

-----------  --------------------

          3  bhanu

/* As shown above because the AUTORETRIEVE ROW feature was turned on the entire row was returned */

Considerations while using Auto generated key feature:

• Inserts will have additional costs when auto generated key retrieval feature is requested.

• This Feature works with a single INSERT or INSERT-SELECT, but not with upsert, merge into, multiload, fastload etc

• Iterated INSERTs have to adhere to the 2048 spool limit of the Array Support feature. A max of 1024 iterations is possible as each iteration uses an AGKR spool and a response spool.