If I understand your matching rule correctly, then you don't need two cursors or the nested loops here. You can use the data from your source table directly e.g. via a cursor FOR loop as shown above, provided you get the data in the right order. Then you want to insert each record into your target table in turn, but only if the NEW_ID does not already exist there.
Here is one way to do this kind of INSERT e.g. imagine that v_old_id and v_new_id are local variables in your PL/SQL code, and v_old_id = 3 and v_new_id = 2. What do you think will happen if a record already exists in the target table with NEW_ID = 2?
There are other ways to do this, but I reckon this should give you everything you need to implement your procedure.
Joined: Feb 04, 2009
Thanks a lot for takingout your precious time to resolve this issue.
actually your suggestion of forlop of cursor is good and readable.
there was an issue with my snippet and i have found it.
Fetch d into v_old_id,v_new_id;
exit when d%notfound; -----> This was missing and therefore it was going to infinite loop