insert missing row in table oracle

I have data in table like below:

Primary_id  Serial_ID   PRIMARY_ID  SECONDARY_ID    queue_ID
1   100 58  89  Q1428291
2   100 58  89  Q1428281
3   100 58  89  Q1428293
4   100 89  58  Q1428293
5   100 89  58  Q1428291
6   100 89  58  Q1428000281
7   200 16  28  Q1433144
8   200 16  28  Q1431953
9   200 16  28  Q1432397
10  200 16  28  Q1431921
11  200 28  16  Q1433144
12  200 28  16  Q1432397
13  200 28  16  Q1431921

We have primary_ID and Secondary_ID column. for serial_ID 100 we have 3 primary and 3 secondary records ..If you see the data Primary_ID become secondary and secondary become primary(3 each).but for serial_id 200 we have 4 primary records but 3 secondry records..

I want to insert missing record in the table.For ex serial_id 200 has missing data for primary ID 28 so insert this into table.Please assist

Answer

You can use something like this. In the temp_table, I put rows into two different types (‘P’ or ‘S’).

I assume that the queue_id = ‘Q1428000281’ (from row 6) is a mistake, and It should be ‘Q1428281’.

drop table table_test ;
create table table_test (id, serial_id, primary_id, secondary_id, queue_id) as (
select 1 ,  100, 58,  89,  'Q1428291'       from dual union all
select 2 ,  100, 58,  89,  'Q1428281'       from dual union all
select 3 ,  100, 58,  89,  'Q1428293'       from dual union all
select 4 ,  100, 89,  58,  'Q1428293'       from dual union all
select 5 ,  100, 89,  58,  'Q1428291'       from dual union all
select 6 ,  100, 89,  58,  'Q1428000281'    from dual union all
select 7 ,  200, 16,  28,  'Q1433144'       from dual union all
select 8 ,  200, 16,  28,  'Q1431953'       from dual union all
select 9 ,  200, 16,  28,  'Q1432397'       from dual union all
select 10,  200, 16,  28,  'Q1431921'       from dual union all
select 11,  200, 28,  16,  'Q1433144'       from dual union all
select 12,  200, 28,  16,  'Q1432397'       from dual union all
select 13,  200, 28,  16,  'Q1431921'       from dual
)
;

insert into table_test (id, serial_id, primary_id, secondary_id, queue_id)
with temp_table as (
select t.*
    , case when primary_id < secondary_id then 'P' else 'S' end type_t
from table_test t
)
select v_max.id_max + rownum ID, SERIAL_ID, PRIMARY_ID, SECONDARY_ID, queue_id
from (
  select SERIAL_ID
    , max(SECONDARY_ID) PRIMARY_ID
    , min(PRIMARY_ID) SECONDARY_ID
    , max(queue_id)queue_id
    , count(TYPE_T) cnt
  from temp_table tp
  group by SERIAL_ID
    , least(PRIMARY_ID, SECONDARY_ID)
    , greatest(PRIMARY_ID, SECONDARY_ID)
    , QUEUE_ID
  having count(TYPE_T) != 2
)t 
cross join (select max(id) id_max from table_test) v_max
;