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:
- 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'
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: