Oracle - SCD Type 2

Question

I am new to Oracle.

I have 2 tables called.

dwh_tbl_dim_supplier_type2_ar (target table)

stg_tbl_dim_supplier_ar (source table)

dwh_tbl_dim_supplier_type2_ar contains six columns

SUPPLIER_KEY (PK)
SUPPLIER_CODE
SUPPLIER_NAME
SUPPLIER_STATE
START_DATE
END_DATE

stg_tbl_dim_supplier_ar contains three columns

SUPPLIER_KEY (PK)
SUPPLIER_NAME
SUPPLIER_STATE

In source table I already have a record SUPPLIER_KEY = 001, SUPPLIER_NAME = Phlogistical Supply Company, SUPPLIER_STATE = IL

In target table I already have a data SUPPLIER_KEY = 001 SUPPLIER_CODE = 001 SUPPLIER_NAME = Phlogistical Supply Company SUPPLIER STATE = CA START_DATE = 2000-01-01 END_DATE = 9999-12-31

From these tables, there are differences in the SUPPLIER_STATE column. I have a task to update TARGET TABLE with data from SOURCE TABLE using SCD Type 2. This method tracks historical data by creating multiple records. Also the result in start_date and end_date column on target table must use date format without time (ex: 2020-06-18) How do I perform this task using SQL MERGE or another query on Oracle? Can anyone explain me step by step with Query? You can see the image below to see what it looks like.

Any help would be greatly appreciated.

Thanks.

Answer 1

This should fits your use case.

MERGE INTO dwh_tbl_dim_supplier_type2_ar DIM
   USING (SELECT 
          A.SUPPLIER_KEY, 
          B.SUPPLIER_CODE,
          A.SUPPLIER_NAME, 
          A.SUPPLIER_STATE
          FROM stg_tbl_dim_supplier_ar A
          LEFT JOIN (SELECT DISTINCT SUPPLIER_CODE, SUPPLIER_KEY FROM dwh_tbl_dim_supplier_type2_ar) B
          ON A.SUPPLIER_KEY = B.SUPPLIER_KEY
         ) STG
    ON DIM.SUPPLIER_KEY = STG.SUPPLIER_KEY
    AND DIM.SUPPLIER_NAME = STG.SUPPLIER_NAME
    AND DIM.SUPPLIER_STATE = STG.SUPPLIER_STATE
   WHEN MATCHED THEN UPDATE SET DIM.END_DATE = TO_DATE(SYSDATE,'DD/MM/YYYY') 
   WHEN NOT MATCHED THEN 
        INSERT (DIM.SUPPLIER_KEY,
                DIM.SUPPLIER_CODE,
                DIM.SUPPLIER_NAME,
                DIM.SUPPLIER_STATE,
                DIM.START_DATE,
                DIM.END_DATE
                )
        VALUES (STG.SUPPLIER_KEY,
                STG.SUPPLIER_CODE,
                STG.SUPPLIER_NAME,
                STG.SUPPLIER_STATE,
                TO_DATE(SYSDATE,'DD/MM/YYYY'),
                TO_DATE('31/12/9999','DD/MM/YYYY')
                )
 ;