15.10 - Generated SQL Insert Statements - Parallel Transporter

Teradata Parallel Transporter User Guide

prodname
Parallel Transporter
vrm_release
15.10
category
User Guide
featnum
B035-2445-035K

Generated SQL Insert Statements

Teradata PT supports one additional feature to reduce script size and eliminate keystroke errors: it will generate 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 Teradata Database table Invoice_Counts has the 4 columns I1, I2, I4 and I8, then Teradata PT will replace "$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 would work equally well. For example:

   $INSERT  @Insert1

will cause Teradata PT to use the value of job variable 'Insert1' as the Teradata Database table name in the resulting generated INSERT statement. In general the more columns a Teradata 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 Teradata 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 will be used as the target table in the generated SQL INSERT statement.

  • If the TargetTable attribute of the consumer operator(s) referenced in the job step collectively identifies a single target table, then that table will be the target table in the generated SQL INSERT statement.
  • Note: 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 will be unable to 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 will terminate the job with an explanatory error message.