Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 07 Jan 2005 @ 11:00:14 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Capturing Updated Rows !
 
From:   Viney_Gusain

Hi,

I've a table with some 40 columns and it is ETLed daily incrementally with approx 15 million rows. Now I need to capture if an existing row in the target got updated by the daily ETL process. This is for logging purposes.

Currently I've written a case when, which is an inequality or clause, at the select level on all the 40 columns to check for the change in the ETL process.

Eg SQL :

     Insert into UPD_TGT
     (C1,C2,...,C40,
     Upd_Flag)
     Select
     TMP.C1,TMP.C2,...,TMP.C40,
     CASE WHEN    TMP.C1  <> TGT.C1
               OR TMP.C2  <> TGT.C2
               ...
               OR TMP.C40 <> TGT.C40
          THEN 'Y'
          ELSE 'N'
     END
     From TMP INNER JOIN TGT

Has anyone else done this thing in another fashion?

I know theoretically of a few ways:

1) Trigger - Put a trigger on the target table but this would hit the system performance.

2) Make row prints using CRC or MD5 or SHA-1 - This sounds like a really good method to use especially the SHA-1 (160 bit). I want to know if anyone has done this in Teradata.


Regards

Viney



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016