Primary Key Constraint Check

Since Exasol does not support cursors, execute all checks on the data within the staging area, and integrate only the transformed and checked data into the target schema. For example:

Initial State: 
- Fact table SALES_POSITIONS
 - PK (SALES_ID, POSITION_ID) 
 - FK (SALES_ID)

– Staging table SALES_POS_UPDATE – This includes only the relevant columns. 

Therefore, only the new sales positions are transferred from the staging table to target schema, leaving the erroneous entries in the staging table with an appropriate error message.

As an example for set-based approach to the above:

UPDATE STG.SALES_POS_UPDATE su
  SET error_text='SALES_ID already exists'
  WHERE EXISTS
    (
      SELECT 1 FROM RETAIL.SALES_POSITIONS s
      WHERE s.SALES_ID = su.SALES_ID
    )
;
INSERT INTO RETAIL.SALES_POSITIONS
  (SELECT SALES_ID, POSITION_ID, ARTICLE_ID, AMOUNT, 
          PRICE, VOUCHER_ID, CANCELED
     FROM STG.SALES_POS_UPDATE su
     WHERE su.ERROR_TEXT IS NULL
  );
DELETE FROM STG.SALES_POS_UPDATE 
WHERE error_text IS NULL;

In many cases, the staging table not only contains new entries but also updates of existing rows. The sales positions that should be removed from the SALES_POSITIONS table might be located in the staging table too. For this purpose, you could omit the primary key check and replace the INSERT by an appropriate MERGE statement as shown below: 

MERGE INTO RETAIL.SALES_POSITIONS s
USING STG.SALES_POS_UPDATE su
   ON s.SALES_ID    = su.SALES_ID 
  AND s.POSITION_ID = su.POSITION_ID
WHEN MATCHED THEN UPDATE SET
      s.VOUCHER_ID = su.VOUCHER_ID
WHEN NOT MATCHED THEN INSERT VALUES
      (SALES_ID, POSITION_ID, ARTICLE_ID, AMOUNT, 
     PRICE, VOUCHER_ID, CANCELED)
;