.BEGIN LOAD SESSIONS 34;
.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
INSERT INTO EQTDBT50.EQTTB535_TAL
POOL_EXPN_DATE =:POOL_EXPN_DATE (DATE, FORMAT 'YYYYMMDD')
.DML LABEL UPSERTDL;
UPDATE EQTDBT50.EQTTB535_TAL SET
.IMPORT INFILE INFILE
APPLY UPSERTAC WHERE (POOL_UPD_CODE = 'C'
OR POOL_UPD_CODE = 'A')
APPLY UPSERTDL WHERE POOL_UPD_CODE = 'D'
/* For the upsert: */
/* (first statement in .DML UPSERTAC) */
/* make sure we have the 50 updates */
.IF &IMP1_UPSERTAC_1 <> 50 THEN
/* ... and 50 inserts */
/* (second statement in .DML UPSERTAC) */
.IF &IMP1_UPSERTAC_2 <> 50 THEN
/* And for the plain update: */
/* (first statement in .DML UPSERTDL) */
/* we should have 10 of these. */
.IF &IMP1_UPSERTDL_1 <> 10 THEN
Teradata TPump supports the Teradata SQL INSERT statement, which adds new rows to a table by directly specifying the row data to be inserted.
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.
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
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.
Alternative to the fieldname clause, an expression that includes one or more actual fieldnames as terms may instead be used
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.