AUTOKEYRETRIEVE - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.00
Published
November 2016
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
hyz1479325149183.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose

Enables users to specify whether the values of any fields associated with Identity Data are returned in response to a SQL Insert operation. Unlike other session control commands, CLI's SPB files (HSHSPB or clispb.dat) do not include a method to specify use of auto generated key retrieval. It can be invoked only at the BTEQ script level through AUTOKEYRETRIEVE command use.

Syntax



where the following is true:

OFF
The initial value for AUTOKEYRETRIVE is OFF. OFF is also the default value when no argument for the command is specified.
ROW
The identity column is included with the resulting row.
COLUMN
Only the identity column associated with INSERT statements is returned.

Usage Notes

Using the AUTOKEYRETRIEVE Command Result
Prior to establishing sessions Using the AUTOKEYRETRIEVE command prior to establishing sessions is valid.

The current AUTOKEYRETRIEVE value remains in effect for all subsequent requests until explicitly changed. It is in effect across LOGONs done within the same BTEQ invocation.

However, if the current database version does not support auto generated key retrieval, the setting is not used.

Example 1 – AUTOKEYRETRIEVE

This example shows the effects of having executed the AUTOKEYRETRIEVE OFF command.

Given the following table definition for an empty table:

CREATE TABLE MyTable
   , NO FALLBACK
   , NO BEFORE JOURNAL
   , NO AFTER JOURNAL
(
      MyCol1  INTEGER GENERATED ALWAYS AS IDENTITY
    , MyCol2  INTEGER
)
PRIMARY INDEX ( MyCol1 )
;

If the following SQL INSERT statement is submitted with AUTOKEYRETRIEVE set to OFF,

INSERT INTO MyTable VALUES(,1);

the result generated shows only an activity count and elapsed-time status messages similar to the following example:

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

Example 2 – AUTOKEYRETRIEVE

This example shows the effects of having executed the AUTOKEYRETRIEVE COLUMN command.

Given the following table definition for an empty table:

CREATE TABLE MyTable
   , NO FALLBACK
   , NO BEFORE JOURNAL
   , NO AFTER JOURNAL
(
      MyCol1  INTEGER GENERATED ALWAYS AS IDENTITY
    , MyCol2  INTEGER
)
PRIMARY INDEX ( MyCol1 )
;

If the following SQL INSERT statement is submitted with AUTOKEYRETRIEVE set to COLUMN,

INSERT INTO MyTable VALUES(,1);

the result generated includes the value generated for the identity column.

*** Insert completed. One row added.
 *** Total elapsed time was 1 second.
     MyCol1
-----------
         2

Example 3 – AUTOKEYRETRIEVE

This example shows the effects of having executed the AUTOKEYRETRIEVE ROW command.

Given the following table definition for an empty table:

CREATE TABLE MyTable
   , NO FALLBACK
   , NO BEFORE JOURNAL
   , NO AFTER JOURNAL
(
      MyCol1  INTEGER GENERATED ALWAYS AS IDENTITY
    , MyCol2  INTEGER
)
PRIMARY INDEX ( MyCol1 )
;

If the following SQL INSERT statement is submitted with AUTOKEYRETRIEVE set to ROW,

INSERT INTO MyTable VALUES(,1);

the result generated includes values for the entire row generated for the insert.

*** Insert completed. One row added.
*** Total elapsed time was 1 second.
    MyCol1          MyCol2
-----------     -----------
         3               1