Insert - Update

Owned by Former user (Deleted)
Last updated: Nov 15, 2017 by Former user (Deleted)Version comment
2 min read

Description

The Insert/Update step first looks up a row in a table using one or more lookup keys. If the row can't be found, it inserts the row. If it can be found and the fields to update are the same, nothing is done. If they are not all the same, the row in the table is updated.

Note: If you have multiple rows with the same keys that match, only the first row found is compared. This may lead to different results, depending on if the found row matches with given values or not. The update scenario looks like this: If a difference is found in the case of multiple rows with the same key, an UPDATE statement is fired against the database that updates all rows with the matching keys. This note also applies to the Update step.

Options

The table below provides a description of available options for the Insert/Update step:

Option

Description

Step name

Name of the step; this name has to be unique in a single transformation.

Connection

The database connection to which data is written

Target schema

The name of the Schema for the table to which data is written. This is important for data sources that allow for table names with periods in them.

Target table

Name of the table in which you want to do the insert or update.

Commit size

The number of rows to change (insert / update) before running a commit.

Don't perform any updates

If enabled, the values in the database are never updated;only inserts are performed.

Key Lookup table

Allows you to specify a list of field values and comparators. You can use the following comparators: =, = ~NULL, <>, <, <=, >, >=, LIKE, BETWEEN, IS NULL, IS NOT NULL

Note: Click Get fields to retrieve a list of fields from the input stream(s).

Update Fields

Allows you to specify all fields in the table you want to insert/update including the keys. Avoid updates on certain fields by specifying N in the update column.

Note: Click Get Update fields to retrieve a list of update fields from the input stream(s).

SQL button

Click SQL to generate the SQL to create the table and indexes for correct operation.


Note: Due to the extra lookup this step performs slower then a normal Table Output step. Another option is to use the Table Output step with error handling what is described in the chapter Step Error Handling. "If you put a primary key on the ID (in this case the customer ID) the insert into the table causes an error. Because of the error handling you can pass the rows in error to the update step. Preliminary tests have shown this strategy of performing upserts to be three times faster in some situations (with a low updates to inserts ratio)."

Metadata Injection Support (7.x and later)

All fields of this step support metadata injection. You can use this step with ETL Metadata Injection to pass metadata to your transformation at runtime.