Insert - Update
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
|
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.
|
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.