15.00 - Using Multistatement Requests To Populate Queue Tables - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Using Multistatement Requests To Populate Queue Tables

You can also use multistatement requests to populate queue tables.

In the following example, three rows are inserted into a queue table named shopping_cart using 1 multistatement request.

    INSERT INTO shopping_cart (order_num, product, quantity) 
     VALUES ('AX3725079002', 'Youth Tee', 2)
    ;INSERT INTO shopping_cart (order_num, product, quantity) 
     VALUES ('AX3725079002', 'Youth Shorts', 4)
    ;INSERT INTO shopping_cart (order_num, product, quantity) 
     VALUES ('AX3725079002', 'Youth Acc', 1);

While this is a fast method of populating a queue table, it does not guarantee that the rows are consumed in the original FIFO order. This is because if you do not supply a QITS value for the individual rows, each INSERT request assigns the same timestamp to the QITS column because the system calculates the default value for CURRENT_TIMESTAMP only once for the entire multistatement request, and it then inserts that value into the QITS column of each row inserted by that request.

You can maintain row‑specific QITS uniqueness and FIFO order for multistatement INSERT requests while also preserving the default CURRENT_TIMESTAMP value by adding a unique interval value to the default current timestamp value.

The following example shows how to do this, incrementing each statement in the request by an arbitrary INTERVAL SECOND value of 0.001 seconds.

    INSERT INTO shoppingcart (qits, ordernum, product, quantity) 
      VALUES (CURRENT_TIMESTAMP, 'AX3725079002', 'Youth Tee', 2)
    ;INSERT INTO shoppingcart (qits, ordernum, product, quantity) 
      VALUES (INTERVAL '0.001' SECOND + CURRENT_TIMESTAMP, 
              'AX3725079002', 'Youth Shorts', 4)
    ;INSERT INTO shoppingcart (qits, ordernum, product, quantity) 
      VALUES (INTERVAL '0.002' SECOND + CURRENT_TIMESTAMP, 
              'AX3725079002', 'Youth Acc', 1);

Related Topics

See “CREATE TABLE (Queue Table Form)” in SQL Data Definition Language Syntax and Examples for the syntax used to create queue tables.