1

Table IA

Id Status  Details  
1   open    aaa  
2   close   bbb  
3   open    ccc  
4   open    ddd  
5   open    eeee  
6   open    ffff  
7   open    gggg  
8   close   iii  
9   close   hhh  
10  open    jjj  
primary key ID

Table snapshot_IA

Id  Status  Details  
1   open    aaa  
2   close   bbb  
3   open    ccc  
4   open    ddd  
5   open    eeee  
4   open    ffff  
5   open    sdsd  
4   open    sdsdd  
  1. IA table data changes daily
  2. Snapshot table does not have primary key
  3. snapshot table should have new rows from table IA
  4. snapshot table should get rows from IA table if IA table ID present in snapshot table but status is open in the snapshot table

eg.

select T1.id1,T1.status,T1.details from   
IA T1,  
snapshot_IA T2  
where  
T1.id1 = T2.ID1  
and T2.status <> 'close'  

One way of doing it is using cursor.

Can you suggest any other efficient way than this?

5
  • Have a look at MERGE
    – McNets
    Nov 22 '18 at 7:58
  • I neither see PL/SQL nor a cursor in your question Nov 22 '18 at 7:59
  • I havent uploaded the code. Just wanted to know different ways to do it. Cursor is one of the option. Nov 22 '18 at 8:04
  • Can we add updated_datetime to the IA table? Nov 24 '18 at 5:30
  • Yes... ........ Nov 25 '18 at 6:37
0
Insert into IA 
 select T1.id1,T1.status,T1.details from   
IA T1 where not exists (
Select 1 from Snapshot_IA WHERE
T1.id1 = T2.ID1  
and T2.status = 'close')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.