Generated SQL Insert Statements - Parallel Transporter

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() );
);

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.