Instead of writing data by using an SQL statement that
is constructed by the stage, you can enter your own INSERT, DELETE,
or UPDATE SQL statement for each Sybase OC input link. Ensure that
the SQL statement contains the table name, the type of update action,
and the columns to write.
Procedure
- Choose User defined SQL from the Update action list
on the General tab of the Input page.
- Click the User-defined tab on the SQL tab.
The User-defined SQL tab opens.
Enter the SQL statement you want to use or edit to write
data to the target Sybase 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 pathname,
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. 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 (?, ?)
If you specify multiple SQL statements,
they are executed as one or more Transact-SQL command batches by using
a semicolon ( ; ) as the end-of-batch signal. You cannot combine multiple
INSERT, UPDATE, and DELETE statements in one batch. You must execute
each in a separate command batch.
You cannot
call stored procedures as there is no facility for passing the row
values as parameters. (You can call stored procedures for output.)
Unless you specify a user-defined SQL statement, the
stage automatically generates an SQL statement.
- Click OK to close
the SYBASEOC Stage dialog box. Changes are saved
when you save your job design.