Archives of the TeradataForum
Message Posted: Fri, 07 Jan 2005 @ 11:00:14 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|