When the Identity Column Teradata Database column attribute is associated with a column, it causes the database to generate a table-level unique number for the column for every inserted row. Starting with Teradata Database V2R6.2, requests that insert into identity columns (INSERT, INSERT ... SELECT, UPSERT, MERGE-INTO) can optionally return a result set containing identity column values (also known as auto-generated keys) for the inserted rows.
An application can specify that auto-generated keys are to be returned from requests that insert into identity columns in the following ways:
- Using the Return Generated Keys configuration option on DSN setup dialog or the ReturnGeneratedKeys option in odbc.ini file.
- Using the ReturnGeneratedKeys connection string keyword in a call to SQLDriverConnect or SQLBrowseConnect
- Using the SQL_ATTR_AGKR connection attribute in a call to SQLSetConnectAttr
- Using the SQL_ATTR_AGKR statement attribute in a call to SQLSetStmtAttr
In all cases, the application can specify that only the identity column is returned for the inserted rows, that all columns of the inserted rows are returned, or that no inserted rows are returned (the default behavior).
When auto-generated key retrieval is enabled, a request that inserts into tables containing identity columns returns two results: a row count with the number of inserted rows and a result set containing the auto-generated keys as a single column or the complete rows inserted, depending on the configuration. The insert request becomes similar to a macro that first inserts and then selects the identity column or all columns of the rows just inserted. The application should call SQLMoreResults to position to the second result and call SQLBindCol/SQLFetch or SQLFetch/SQLGetData to retrieve the result set containing the generated keys.