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 the 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 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.