Copy records from one table to another with pl-sql

advertisements

I want to copy records from one table to another. The only records from table 1 that will be copied to table 2 are the ones that still dont exist in table 2. If duplicate records exists in Table 1 then only be copied to table 2 the record with the larger size name.

I could already implement a query that almost does what I want. The problem I have is when there are names with the same maximum size of characters. In these cases, my query returns more than one record and I just want to insert one new record in table 2.

Does anyone know how I can fix this?

Here is my code:

For x in (Select distinct xdd.id_t, xdd.name_t
           From table1 xdd
          Where xdd.id_t not in (Select distinct det.id_t2
                                  From table2 det)
            And LENGTH(xdd.name_t) in (Select Max(LENGTH(xdd2.name_t))
                                        From table1 xdd2
                                       Where xdd2.id_t = xdd.id_t)
         ) Loop

    Insert into id_t2 (id_t2, name_t2)
       Values (x.id_t, x.name_t);         

End loop;


Can you give me an example to solve this? Sure. If I understood requirements correctly, then the merge statement will look similar to this one:

We use row_number() analytic function to choose a duplicate record with longer name_t

merge into table_two t2
  using(
        select id_t
             , name_t
          from (select id_t
                     , name_t
                     , row_number() over(partition by id_t
                                             order by length(name_t) desc) as rn
                  from table_one) q
                 where q.rn = 1
         ) t1
     on (t2.id_t = t1.id_t)
when not matched then
   insert(id_t, name_t)
     values(t1.id_t, t1.name_t)

SQLFiddle demo