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’.

XU_Vertica_Vertica_DBVusializer_WERKS1000

Let’s alter the field content with the value ‘WERK HAMBURG’.

XU_Vertica_Vertica_DBVisualizer_UPDATE

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.

XU_Vertica_Vertica_Merge_Setting

The prepare merge statement creates a temporary staging table on the database.

XU_Vertica_Vertica_SQL_Prepare_Merge

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.

XU_Vertica_Vertica_SQL_Finalize_Merge