Archives of the TeradataForum

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

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

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>>