Staging Upsert

Adding the output type StagingUpsert to your transformation:

  1. Click the Design tab.
  2. Select the Output tab.
  3. Drag the output type StagingUpsert into your transformation or double-click the transformation to bring it into the transformation flow.
    By double-clicking, the link between the two steps it is automatically set up. See this diagram:
    ../images/image2.png
    You can also link these stages by pressing SHIFT and click DataLakeInput and draw a line towards the StagingUpsert stage.
    Double-click the box, the Staging Upsert dialog box is displayed.
  4. Specify a name for the step and specify the Target Table name, for example CMNDIV.
  5. Click Get default for the key(s) and based on the metadata it automatically retrieves the key fields for this object.
  6. Click Get update fields.
    Based on the variation number it automatically checks the variation number of the transaction on the specific keys weather the record is going to be updated or not.
  7. Click SQL to generate the table within the staging database and with the required keys.
  8. Click Close and click OK.

    Eventually, to improve the look-up performances, an index can be created on the table for Dl_document_date:

    CREATE INDEX idx_<tablenm>_dlDocumentDate ON <tablenm> (dlDocumentDate)