SELECT AND CONSUME INTO | Teradata Vantage - SELECT AND CONSUME ... INTO - Advanced SQL Engine - Teradata Database

Selects data from the row with the oldest insertion timestamp in the specified queue table, deletes the row from the queue table, and assigns the values in that row to host variables in an embedded SQL application or to local variables or parameters in stored procedures.

ANSI Compliance

SELECT AND CONSUME ... INTO is a Teradata extension to the ANSI/ISO SQL:2011 standard.

Required Privileges

To execute a SELECT AND CONSUME ... INTO from a queue table, you must have the SELECT and DELETE privileges on that table.

Invocation

Executable.

Stored procedures and embedded SQL.

Stored Procedure Syntax

{ SELECT | SET } AND CONSUME TOP 1 select_list
   INTO into_spec [,...] FROM queue_table_name

into_spec

[:] { local_variable_name | parameter_name }

Embedded SQL Syntax

{ SELECT | SET } AND CONSUME TOP 1 select_list
   INTO into_spec [,...] FROM queue_table_name

into_spec

[:] host_variable_name [ [INDICATOR] :host_indicator_name ]

Syntax Elements

select_list

An ASTERISK character (*) or a comma-separated list of valid SQL expressions.

If select_list specifies *, all columns from the queue table specified in the FROM clause are returned.

The select list must not contain aggregate or ordered analytical functions.

local_variable_name

The name of the local variable declared in the stored procedure into which the SELECTed data is to be placed.

You cannot use stored procedure status variables here.

queue_table_name

The name of a queue table that was created with the QUEUE option in the CREATE TABLE statement.

parameter_name

The name of the stored procedure parameter into which the SELECTed data is to be placed.

Only output parameters (INOUT and OUT type) can be specified.

host_variable_name

The name of the host variable into which the selected data is to be placed.

host_indicator_name

The name of the host indicator variable.

Usage Notes

• Attributes of a Queue Table

  A queue table is similar to an ordinary base table, with the additional unique property of behaving like an asynchronous first-in-first-out (FIFO) queue.

  The first column of a queue table contains Queue Insertion TimeStamp (QITS) values. The CREATE TABLE statement must define the first column with the following data type and attributes:

  TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)

  The QITS value of a row indicates the time the row was inserted into the queue table, unless a different, user-supplied value is inserted.

• Using a Colon Character in Embedded SQL

  In embedded SQL, blanks before and after a colon character are optional; use of the colon character before host_variable_name is optional; a colon character must precede a host_indicator_name.

• Rules for Embedded SQL

  The same rules that apply to SELECT ... INTO apply to SELECT AND CONSUME ... INTO.

Related Information

• queue_table_name, see the information about CREATE TABLE (Queue Table Form) in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
• Usage notes, information on transaction processing, locks, and restrictions, see the information about SELECT AND CONSUME in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
• The rules that apply to SELECT ... INTO and SELECT AND CONSUME ... INTO, see the rules for embedded SQL in SELECT ... INTO.