Data Manipulation Language statements for data manipulation

The connector uses Data Manipulation Language (DML) statements to manipulate data in data sources.

For the source context (output links), the SELECT statements select data from a data source during a READ operation.

For the target context (input links) the INSERT, UPDATE, DELETE, INSERT then UPDATE, UPDATE then INSERT, and DELETE then INSERT statements work with the specified data in a data source during a WRITE operation. The multiple-action statements, such as INSERT then UPDATE, specify the order in which these statements are run.

These statement properties are all sub-properties of the SQL property on the Properties tab.

You can create your DML SQL statement in several different ways:
  • Type your statement in the property value.
  • Open a small statement window to more easily type a multiple-line statement. Click in the property value and then press ellipses button to open this window.
  • Use the SQL builder to graphically build your statement. Press Build at the end of the statement value to select the version of SQL builder that you want to use and to open the SQL builder in that version.
  • Automatically generate a SQL statement at run time.
When you use either of the first two methods, make sure that you specify an association between each table column and each statement parameter in your SQL statement. If you do not do this, only limited schema reconciliation is performed. The following example is a statement where the association between columns and statement parameters has not been made:
INSERT INTO Table VALUES(ORCHESTRATE.A, ORCHESTRATE.B)
The table columns in Table were not identified. The following example is a correct statement that includes the associations with columns X and Y:
INSERT INTO Table(X,Y) VALUES(ORCHESTRATE.A, ORCHESTRATE.B)