Using User-Defined SQL Statements

Instead of writing data using an SQL statement constructed by the stage, you can enter your own SQL INSERT, DELETE, or UPDATE statement for each Teradata input link.

1. Choose User-defined SQL from the Update action drop-down list box on the General tab of the Input page.
2. Click the SQL tab, then the User-defined tab. The User-defined tab page opens.

   By default you see the stage-generated SQL statement. You can edit this statement or enter your own SQL statement to write data to the target Teradata tables. This statement must contain the table name, the type of update action you want to perform, and the columns you want to write.

   If the property value begins with {FILE}, the remaining text is interpreted as a path name, and the contents of the file supplies the property value.

   When writing data, the INSERT statements must contain a VALUES clause with a parameter marker ( ? ) for each stage input column. UPDATE statements must contain a SET clause with parameter markers for each stage input column. UPDATE and DELETE statements must contain a WHERE clause with parameter markers for the primary key columns. If you specify multiple SQL statements, each is executed as a separate transaction. Terminate individual SQL statements with a semicolon ( ; ). Use a double semicolon ( ;; ) to indicate the end of the command batch. You cannot combine multiple INSERT, UPDATE, and DELETE statements in one batch. You must execute each statement in a separate command batch.

   The parameter markers must be in the same order as the associated columns listed in the stage properties. For example:

   INSERT emp (emp_no, emp_name) VALUES (?, ?)

   The size of this box changes proportionately when the main window is resized to conveniently display very long or complex SQL statements.

   Unless you specify a user-defined SQL statement, the stage automatically generates an SQL statement.

3. Click OK to close this dialog box. Changes are saved when you save your job design.