How to produce “merge” of two data sets with selec

Question

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

Answer 3

Roman, you need to do a full outer join on your select so that, for any given event type id (and I am assuming that you will have 0 or 1 row in both your past and future tables for any particular event type id), you will output a single row, and the appropriate null and non-null dates.

The first answer will not combine the dates from the two sources into a single row, which is what I believe you asked for.

The second answer was closer, but would only work for rows with both a past and future date (ie: one in each table) due to the inner join.

Example:

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


Note that the nvl function is for Oracle and will use the first non-null it sees. ISNULL may be used for SQL Server.