You populate queue tables in the same way you populate nonqueue tables: with INSERT statements, INSERT … SELECT statements, or using a load utility like Teradata Parallel Data Pump or Teradata Parallel Transporter (using the STREAM and INSERT operators) that generates those statements.
You can use a simple INSERT … SELECT statement to populate a queue table from either a queue- or a nonqueue table. You can also use an INSERT … SELECT AND CONSUME statement to populate a queue table with rows from another queue table.
You do not insert the QITS value. The system inserts the QITS value by default, using the value for CURRENT_TIMESTAMP.
Similarly, you do not insert the QSN value if your queue table uses a system-generated identity column.
The source table for an INSERT … SELECT statement can be either another queue table or a nonqueue table.
The following example ANSI session mode transaction uses an INSERT statement and multiple INSERT … SELECT requests to insert rows into a queue table named shopping_cart.
INSERT INTO shopping_cart (order_num, product, quantity)
VALUES ('I07219100', 'dozen baseballs' , 1);
INSERT INTO shopping_cart (order_num, product, quantity)
SELECT order_num, product, quantity
FROM mail_orders;
INSERT INTO shopping_cart (order_num, product, quantity)
SELECT order_num, product, quantity
FROM backorder_tbl
WHERE order_num = 'I00200314';
COMMIT;
Using Multiple-Statement Requests to Populate Queue Tables
You can also use multiple-statement requests to populate queue tables.
In the following example, three rows are inserted into a queue table named shopping_cart using 1 multiple-statement 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);
This method of populating a queue table is fast, but does not guarantee that rows are consumed in original FIFO order. 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 once for the multiple-statement request, and 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 multiple-statement 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);