SQL INSERT, UPDATE and DELETE based on source table

Question

I have a source table with fields ID NAME and DESIG

create or replace table emp_source
(
    id number, 
    name string,
    desig string
);

And my destination table has columns ID NAME DESIG META_CRT_DT (date when record is inserted) META_UPD_DT (date when record is updated) META_IUD_FLG (to be updated based on Insert, Delete or Update operation performed) CURRENT_FLAG (if a record is deleted, I must make this false)

create or replace table emp_des
(
    id number, 
    name string, 
    desig string,
    META_CRT_DT timestamp_ntz,
    META_UPD_DT timestamp_ntz,
    META_IUD_FLG char,
    current_flag boolean
);

I'm inserting new record like this

INSERT INTO emp_des (id, name, desig,META_CRT_DT, 
                     meta_upd_dt, meta_iud_flg, current_flag)
SELECT
    id, name, desig, 
    to_date(current_timestamp) as crt,   
    to_date(current_timestamp) as upd, 
    'I' as iud, TRUE as flag 
FROM
    emp_source 
WHERE
    NOT EXISTS (SELECT * FROM emp_des 
                WHERE emp_source.id = emp_des.id);

When a record is updated in source table, say the designation is changed, in the EMP_DES table I need to update the DESIG, META_UPD_DT and META_IUD_FLG as 'U'.

Similarly when a record is deleted I need to update the META_UPD_DT and META_IUD_FLG as 'D'.

I'm new to SQL, so any help is appreciated. Thank you.

When a record is updated in source table, say the designation is changed, in the EMP_DES table I need to update the DESIG, META_UPD_DT and META_IUD_FLG as 'U'. I tried this but it changes all flags to 'U'.

UPDATE emp_des 
SET desig = s.desig, 
    meta_upd_dt = to_date(current_timestamp), 
    meta_iud_flg = 'U', 
    current_flag = 'TRUE' 
FROM emp_source AS s 
WHERE EXISTS (SELECT * FROM emp_des WHERE id = emp_des.id);

Similarly when a record is deleted I need to update the META_UPD_DT and META_IUD_FLG as 'D'.

I'm new to SQL, so I'd like to make it as simple as possible. Thank you for taking your time.

Answer 1

You asked for a non-trigger approach (Is there any way to do updates without triggers? Like update will take place only when i run the command?). Whilst I strongly prefer a trigger based approach, here is a sample stored procedure:

Note that, unlike triggers, use of "current_timestamp" will not be when each individual event actually occurred, instead it will be when the stored procedure was executed, and these are not the same things at all.

CREATE OR REPLACE PROCEDURE update_emp_des()
AS
BEGIN
    -- Update existing records
    UPDATE emp_des
    SET desig = s.desig,
        meta_upd_dt = CURRENT_TIMESTAMP,
        meta_iud_flg = 'U',
        current_flag = TRUE
    FROM emp_source AS s
    WHERE emp_des.id = s.id AND emp_des.desig != s.desig;

    -- Insert new records
    INSERT INTO emp_des (id, name, desig, META_CRT_DT,
                         meta_upd_dt, meta_iud_flg, current_flag)
    SELECT id, name, desig,
           CURRENT_TIMESTAMP AS crt,
           CURRENT_TIMESTAMP AS upd,
           'I' AS iud, TRUE AS flag
    FROM emp_source
    WHERE NOT EXISTS (SELECT * FROM emp_des WHERE emp_source.id = emp_des.id);

    -- Delete records
    UPDATE emp_des
    SET meta_upd_dt = CURRENT_TIMESTAMP,
        meta_iud_flg = 'D',
        current_flag = FALSE
    WHERE NOT EXISTS (SELECT * FROM emp_source WHERE emp_source.id = emp_des.id);
END;

This stored procedure updates the desig, meta_upd_dt, and meta_iud_flg columns for existing records in the emp_des table where the desig column has changed in the emp_source table.

It also inserts new records from the emp_source table into the emp_des table and updates the meta_upd_dt, meta_iud_flg, and current_flag columns for records that have been deleted from the emp_source table.

You could break this stored proc into the 3 parts and rune them separately and this may be useful if volumes are large and you want to minimize any locking effects (another disadvantage of the stored proc approach is how to mange the locks).