Primary key constraint check

Since Exasol does not support cursors, the best practice is to execute all checks on the data within the staging area, and then integrate only the transformed and checked data into the target schema.

In the following example, we have a fact table SALES_POSITIONS with primary keys SALES_ID and POSITION_ID and the foreign key SALES_ID, and a staging table SALES_POS_UPDATE (which only includes the relevant columns). Only new sales positions are transferred from the staging table to target schema, while invalid entries remain in the staging table with an appropriate error message.

Example (set-based approach):

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
;

The staging table can here contain not only new entries but also updates of existing rows. Hence, the sales positions that are removed from the SALES_POSITIONS table in the previous example may also exist in the staging table. To prevent this, omit the primary key constraint check and use a MERGE statement instead of the INSERT statement.

Example: 

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)
;