APPLY Example
.BEGIN LOAD SESSIONS 34;
.LAYOUT EQTTB535;
.FIELD Pool_Upd_Code * CHAR(01);
.FIELD Eqmt_Init * CHAR(04);
....
.DML LABEL UPSERTAC
DO INSERT FOR MISSING UPDATE ROWS;
UPDATE EQTDBT50.EQTTB535_TAL SET
TCS_POOL_IDFR_NUM =:TCS_POOL_IDFR_NUM
.....
WHERE
.....
;
INSERT INTO EQTDBT50.EQTTB535_TAL
VALUES(
POOL_EXPN_DATE =:POOL_EXPN_DATE (DATE, FORMAT 'YYYYMMDD')
.....
);
.DML LABEL UPSERTDL;
UPDATE EQTDBT50.EQTTB535_TAL SET
.....
WHERE
.....
;
.IMPORT INFILE INFILE
LAYOUT EQTTB535
APPLY UPSERTAC WHERE (POOL_UPD_CODE = 'C'
OR POOL_UPD_CODE = 'A')
APPLY UPSERTDL WHERE POOL_UPD_CODE = 'D'
;
.END LOAD;
/* For the upsert: */
/* (first statement in .DML UPSERTAC) */
/* make sure we have the 50 updates */
.IF &IMP1_UPSERTAC_1 <> 50 THEN
.LOGOFF 100;
/* ... and 50 inserts */
/* (second statement in .DML UPSERTAC) */
.IF &IMP1_UPSERTAC_2 <> 50 THEN
.LOGOFF 101;
/* And for the plain update: */
/* (first statement in .DML UPSERTDL) */
/* we should have 10 of these. */
.IF &IMP1_UPSERTDL_1 <> 10 THEN
.LOGOFF 102;
.LOGOFF;
INSERT
Purpose
Teradata TPump supports the Teradata SQL INSERT statement, which adds new rows to a table by directly specifying the row data to be inserted.
Syntax
where
|
Syntax Element |
Description |
|
tname |
Table that is to receive rows created from input data records If the table is not explicitly qualified by database name, the default database qualifies it. |
|
cname |
Column of the specified table that is to receive the value from a field of matching input records, where the value is identified by the corresponding entry in the fieldname list |
|
fieldname |
Field of an input record, whose value is given to a column of the specified table that is identified by the corresponding entry in the cname clause in this statement If this statement did not specify a cname, the object’s CREATE statement provides the corresponding column identifier. This assumes that all columns from the table correspond to those specified in the original CREATE statement. |
|
expression |
Alternative to the fieldname clause, an expression that includes one or more actual fieldnames as terms may instead be used |
Usage Notes
The following notes describe how to use an INSERT statement following a DML command. An INSERT statement may also be used in the support environment; normal rules for INSERT are followed in that case.
One way of specifying the applicable DML statements is to relate each field name to the name of the column to which the field’s data is applied. Another way tells Teradata TPump to apply the first nonfiller field of a record that is sent to Teradata Database to the first defined column of the affected table, the second nonfiller field to the second column, and so on.
Teradata TPump converts INSERT statements into macro equivalents and, depending on the packing specified, submits multiple statements on one request.
To insert records into the table identified by tname, the username specified in the LOGON command must have the INSERT privilege for the table.
A value must be specified for every column, either explicitly or by default.
For Teradata TPump use, if the object of the INSERT statement is a view, it must not specify a join. Teradata TPump operates only on single table statements so INSERT statements must not contain any joins.
The correspondence between the fields of data records to be inserted into a table, and the columns of the table, can be specified in any of four ways. These appear in the following examples, using targetable as the table or view name.
The maximum number of INSERT, UPDATE, and DELETE statements that can be referenced in an IMPORT is 128. The 128th DML which would cause the insertion of the DML sequence number of 128 for the DMLSEQ field in the error table could lead to Teradata Database 3520 error.
The maximum number of DML statements that can be packed into a request is 1500. The default number of statements packed is 20.
ANSI/SQL DateTime Specifications
The ANSI/SQL DATE, TIME, TIMESTAMP, and INTERVAL DateTime data types can be used in Teradata SQL CREATE TABLE statements. Specify the data types as column/field modifiers in INSERT statements.