In this example we will run an extraction to merge data, i.e. insert a new data row or update an existing one. Prerequisite for executing the merge is defining a primary key in the database table.
It is important that the destination table has the right index to execute the merge statement with good performance.
Without an index merging data may take very long depending on the amount of data.
Let’s look at the data row for plant 1000. The field NAME2 has the value ‘Werk’.
Let’s alter the field content with the value ‘WERK HAMBURG’.
Now let’s change the Vertica Destination Settings and set
- Preparation to Prepare Merge
- Row Processing to Fill merge staging table
- Finalization to Finalize Merge
to merge the data.
The prepare merge statement creates a temporary staging table on the database.
The finalize merge statement inserts a new data row and updates an existing one. Afterwards the staging table created in the Preparation step will be deleted.