Update Action in DataStage

Specifies which SQL statements are used to update the target table. Some update actions require key columns to update or delete rows. There is no default. Choose the option you want from the list:

Clear table then insert rows. Deletes the contents of the table and adds the new rows, with slower performance because of transaction logging. This is the default value.

Truncate table then insert rows. Truncates the table with no transaction logging and faster performance. For DB2/UDB and Informix, this option is the same as Clear table then insert rows.

Insert rows without clearing. Inserts the new rows in the table.

Delete existing rows only. Deletes existing rows in the target table that have identical keys in the source files.

Replace existing rows completely. Deletes the existing rows, then adds the new rows to the table.

Update existing rows only. Updates the existing data rows. Any rows in the data that do not exist in the table are ignored.

Update existing rows or insert new rows. Updates the existing data rows before adding new rows. It is faster to update first when you have a large number of records.

Insert new rows or update existing rows. Inserts the new rows before updating existing rows. It is faster to insert first if you have only a few records.

Insert new rows only. Inserts the new rows in the table but does not report duplicate rows to the log.

Truncate only. Ignores any incoming data and truncates the target table.

Important. A stage cannot stand alone on the canvas. Configure a dummy source stage. To keep the process simple and to preserve the resource, make sure the dummy source stage does not have any source data.

User-defined SQL. Writes the data using a user-defined SQL statement, which overrides the default SQL statement generated by the stage. If you choose this option, you enter the SQL statement on the SQL tab.

User-defined SQL file. Reads the content of the specified file to write the data.