Generated SQL Insert Statements - Parallel Transporter

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