CODEWITHSUNDEEP
sqloracle-database
user93796
user93796

Reputation: 18379

merging records from two tables into third using SQL

I have 3 tables A, B, C

Schema of all 3 tables is same as mentioned below:

id, time1, place, xyz, abc, pqrs

Now both tables A and B have many records ( around 10 million)

Now some of the records in A and B are with same id and some are with different Id

Now I want to merge the records from A , and B into Table C. Merge logic is as follows

1)If records with id = someId is present only in A or B ( only in one table)
    then insert record from that table (A/B depending of where its present) in table C
2)If records with id =someId is present in both A and B {

           if(A.time1 > B.time2){
             insert record from A into C
          }else{
             insert record from B into C
          }
    }

I want to do it using a single SQL query , by using outer join between A and B. But I am not very clear how to go about it.

Upvotes: 0

Views: 2593

Answers (5)

Jafar Kofahi
Jafar Kofahi

Reputation: 763

Assuming table C is empty, then something like this would do the trick

INSERT INTO C           
SELECT A.* 
  FROM A LEFT OUTER JOIN B
  ON A.ID = B.ID
 WHERE (A.TIME1 > B.TIME1 OR B.TIME1 IS NULL)
UNION 
SELECT B.*
  FROM B LEFT OUTER JOIN A
  ON B.ID = A.ID
 WHERE (A.TIME1 <= B.TIME1 OR A.ID IS NULL)

Upvotes: 2

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

Using UNION, you can try something like this :

INSERT INTO C
SELECT A.* 
FROM A 
LEFT OUTER JOIN B ON A.ID = B.ID 
WHERE A.TIME1 > B.TIME1 OR B.ID IS NULL
UNION 
SELECT B.*
FROM B 
LEFT OUTER JOIN A ON B.ID = A.ID 
WHERE B.TIME1 > A.TIME1 OR A.ID IS NULL

SQLFIDDLE : http://www.sqlfiddle.com/#!4/5019b/2/0

Upvotes: 1

the_slk
the_slk

Reputation: 2182

CREATE TABLE t_c
(
    id_c   NUMBER
,   time_c DATE
);

INSERT
WHEN    id_a IS NULL THEN INTO t_c(id_c, time_c) VALUES (id_b, time_b)
WHEN    id_b IS NULL THEN INTO t_c(id_c, time_c) VALUES (id_a, time_a)
WHEN    id_a = id_b AND time_a >  time_b THEN INTO t_c(id_c, time_c) VALUES (id_a, time_a)
WHEN    id_a = id_b AND time_a <= time_b THEN INTO t_c(id_c, time_c) VALUES (id_b, time_b)
SELECT  *
FROM
(
        SELECT  1 id_a, DATE'2013-01-01' time_a FROM dual UNION ALL
        SELECT  2 id_a, DATE'2013-01-02' time_a FROM dual UNION ALL
        SELECT  3 id_a, DATE'2013-01-03' time_a FROM dual UNION ALL
        SELECT  4 id_a, DATE'2013-01-04' time_a FROM dual UNION ALL
        SELECT  7 id_a, DATE'2013-01-07' time_a FROM dual UNION ALL
        SELECT  8 id_a, DATE'2013-01-08' time_a FROM dual
) t_a
FULL    JOIN
(
        SELECT  1 id_b, DATE'2013-01-01' time_b FROM dual UNION ALL
        SELECT  2 id_b, DATE'2013-01-02' time_b FROM dual UNION ALL
        SELECT  5 id_b, DATE'2013-01-05' time_b FROM dual UNION ALL
        SELECT  6 id_b, DATE'2013-01-06' time_b FROM dual UNION ALL
        SELECT  7 id_a, DATE'2013-01-09' time_a FROM dual UNION ALL
        SELECT  8 id_a, DATE'2013-01-01' time_a FROM dual
) t_b
ON      t_a.id_a = t_b.id_b
;
/*
1   2013-01-01 00:00:00     1   2013-01-01 00:00:00
2   2013-01-02 00:00:00     2   2013-01-02 00:00:00
                            5   2013-01-05 00:00:00
                            6   2013-01-06 00:00:00
7   2013-01-07 00:00:00     7   2013-01-09 00:00:00
8   2013-01-08 00:00:00     8   2013-01-01 00:00:00
4   2013-01-04 00:00:00     
3   2013-01-03 00:00:00     
*/

SELECT  *
FROM    t_c;
/*
5   2013-01-05 00:00:00
6   2013-01-06 00:00:00
4   2013-01-04 00:00:00
3   2013-01-03 00:00:00
8   2013-01-08 00:00:00
1   2013-01-01 00:00:00
2   2013-01-02 00:00:00
7   2013-01-09 00:00:00
*/

Upvotes: 0

collapsar
collapsar

Reputation: 17238

combine 3 result sets:

  1. records with shared ids.
  2. records with ids only present in a
  3. records with ids only present in b

1., 2. can be combined into 1 left join, 3. is a right join filtered for unique id occurrence. choose a as data source for 2., b for 3., and a/b depending on precedence for 1.; the selection logic can be compacted to a single condition as the time1 comparisons will only yield a boolean if none is null, ie. on record from 1.

    insert 
      into c ( id, time1, place, xyz, abc, pqrs )
         (
            select id1
                 , time1
                 , place
                 , xyz
                 , abc
                 , pqrs
              from (
                        select a1.id    id1
                             , b1.id    id2
                             , CASE b1.time1 > a1.time1 then b1.time1   else a1.time1   end time1
                             , CASE b1.time1 > a1.time1 then b1.place   else a1.place   end place
                             , CASE b1.time1 > a1.time1 then b1.xyz     else a1.xyz     end xyz
                             , CASE b1.time1 > a1.time1 then b1.abc     else a1.abc     end abc
                             , CASE b1.time1 > a1.time1 then b1.pqrs    else a1.pqrs    end pqrs
                          from a a1
                     left join b b1 on ( b1.id = a1.id )
                         union
                        select b2.id    id1
                             , a2.id    id2
                             , b2.time1
                             , b2.xyz
                             , b2.place
                             , b2.abc  
                             , b2.pqrs                                      , 
                          from b b2
                     left join a a2 on ( a2.id = b2.id )
                         where a2.id is null
                   ) pairs
         )
         ;

Upvotes: 0

Emmanuel
Emmanuel

Reputation: 14209

In a single request:

insert into C
  -- Outer join on B: if B is null null or A has greater time take A
  select case when B.id is null or A.time1 > B.time1 then A.id else B.id end,
         case when B.id is null or A.time1 > B.time1 then A.time1 else B.time1 end,
         case when B.id is null or A.time1 > B.time1 then A.place else B.place end,
         case when B.id is null or A.time1 > B.time1 then A.xyz else B.xyz end,
         case when B.id is null or A.time1 > B.time1 then A.abc else B.abc end,
         case when B.id is null or A.time1 > B.time1 then A.pqrs else B.pqrs end
  from A, B
  where B.id (+) = A.id
union all
  -- Then get the ones in B and not in A with outer join on A
  select B.id, B.time1, B.place, B.xyz, B.abc, B.pqrs
  from B, A
  where A.id (+) = B.id
    and A.is is null

Upvotes: 0

Related Questions