Generated SQL Insert Statements - Parallel Transporter

Teradata® Parallel Transporter User Guide - 17.20

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
Parallel Transporter
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-08-25
dita:mapPath
uzp1645128359760.ditamap
dita:ditavalPath
tvt1507315030722.ditaval
dita:id
B035-2445
Product Category
Teradata Tools and Utilities

Teradata PT supports one additional feature to reduce script size and eliminate keystroke errors: it generates any SQL INSERT statement, if the target table is specified or can be unambiguously determined from your script. For example, if an SQL INSERT statement is coded in your script as

$INSERT 'Invoice_Counts'

and database table Invoice_Counts has the 4 columns I1, I2, I4 and I8, then Teradata PT replaces "$INSERT 'Invoice_Counts'" in your script with the following generated SQL INSERT statement:

'INSERT INTO Invoice_Counts VALUES (
          :I1,
          :I2,
          :I4,
          :I8);'

Using a job variable you name to identify the target table works equally well. For example:

$INSERT  @Insert1

will cause Teradata PT to use the value of job variable 'Insert1' as the database table name in the resulting generated INSERT statement. In general the more columns a database table has, the more useful the $INSERT macro is, if the job script requires an SQL INSERT statement for that table.

The use of table-specifying $INSERT macros makes possible APPLY statements such as the following:

STEP LOAD_QRTRS
(
  APPLY

    CASE

      WHEN( TRANS_DATE <= '2011-03-31' )

        THEN  $INSERT 'Q1_Trans'

      WHEN( TRANS_DATE <= '2011-06-30' )

        THEN  $INSERT 'Q2_Trans'

      WHEN( TRANS_DATE <= '2011-09-30' )

        THEN  $INSERT 'Q3_Trans'

      ELSE  $INSERT 'Q4_Trans'

    END

  TO OPERATOR( $LOAD() )

  SELECT * FROM OPERATOR( $FILE_READER() );
);
The use of $INSERT without a database table specifier is still supported in any job step, if Teradata PT can identify the target table by the following procedure:
  • If the job-scope job variable
    TargetTable

    has been assigned a value, then that value is used as the target table in the generated SQL INSERT statement.

  • If the TargetTable attribute of the consumer operators referenced in the job step collectively identifies a single target table, then that table is the target table in the generated SQL INSERT statement.
    While using consumer operator templates like $LOAD and $UPDATE, the TargetTable attribute needs to be specified explicitly in the script, in the job variable file, or as part of the consumer operator's attribute specification within the job step; otherwise, TPT cannot pick up the TargetTable attribute value for generating the SQL INSERT statement.

If the target table of an instance of $INSERT without a table specifier is ambiguous or cannot be identified at all, then Teradata PT terminates the job with an explanatory error message.