17.10 - Using Multistatement Requests To Populate Queue Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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 statement 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 statements 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);