How to produce "merge" of two data sets with select SQL statement (Oracle DBMS)?

Question

Hi:

What would be the insert SQL statement to merge data from two tables. For example I have events_source_1 table (columns: event_type_id, event_date) and events_source_2 table (same columns) and events_target table (columns: event_type_id, past_event_date nullalbe, future_event_date nullable).

Events_source_1 has past events, Events_source_2 has future events and resultant events_target would contain past and future events in the same row for same event_type_id. If there is no past_events but future_events then past_event_date won't be set and only future_event_date will be and the opposite is true too.

Thanks a lot in advance for helping me resolving this problem.

Answer 1

INSERT INTO events_target
(
SELECT event_type_id, 
       NULL as past_event_date, 
       event_date as future_event_date 
  FROM Events_source_2
UNION
SELECT event_type_id, 
       event_date as past_event_date, 
       NULL as future_event_date 
  FROM Events_source_1
)

As long as event_type_id is unique between Events_source_1 and Events_source_2

Answer 2

Maybe something like this: (Not 100% sure on the syntax)

insert into event_target (event_type_id, past_event_date, future_event_date)
select
    p.event_type_id as event_type_id,
    p.event_date as past_event_date,
    f.event_date as future_event_date
from events_source_1 p
inner join events_source_2 f on p.event_type_id = f.event_type_id