How do I insert a record when comparing two tables?

[john9]

How can I insert a record into a table when comparing two other tables? In the code shown below I am comparing two different tables. When a 'not exist' condition occurs I want to insert a new record into a third table.

=============================================
select system_id, cycle_date
from commdev.comm_agent_balances_hdr_stg1 h

where not exists
(select system_id, cycle_date from commdev.comm_hdr_trl c
where h.system_id = c.system_id
and h.cycle_date = c.cycle_date);

insert into commdev.comm_agent_balance_status
(status_system_id ,
status_cycle_date ,
status_message)
select
h.system_id ,
h.cycle_date ,
'ERROR'
from commdev.comm_agent_balances_hdr_stg1 h;

 

[sem]

I can not recognize your problem. Why don't you use

insert into <your table>
select system_id, cycle_date
from commdev.comm_agent_balances_hdr_stg1 h
where not exists
(select system_id, cycle_date from commdev.comm_hdr_trl c
where h.system_id = c.system_id
and h.cycle_date = c.cycle_date);

in an ordinary way?