Update strategy question...

[jisoo23]

I have a situation that I'm not quite sure how to handle. Basically I have a source table and target table, both have almost the same data but a few rows have columns with different data. I'm supposed to compare three columns from each row in the source and target tables, and when I find a target table column that has differing data, I update that column with the data from the source table. Does anyone have a suggestion on an efficient way to do this?

Currently I have the source/source qualifer, an expression transformation, and lookup transformation set up. The lookup references the target table in order to bring the data in for comparison. The data from the expression and lookup are moved to another expression transformation. Is it here that I would use some sort of IIF statement to make comparisons and update? Any help is appreciated.

Thanks,
Jisoo23

 

[bambam229]

Update strategies are expensive. Do the comparison in an expression that sets an integer flag. For example in an output port of an expression say: IIF(src_COL1 != tgt_COL1 or src_COL2 != tgt_COL2, 1, 0)

Then in a filter have the expression filter out all rows were the integer flag = 0.

Finally, change you session to be "Treat Rows As": Update

That way you can avoid the expensive update strategy.

Hope that helps..

 

[blom0344]

I wonder if you need ETL in this case anyway. Looks to me that a batch job with 3 SQL statements will do a very nice job in this case:

UPDATE TARGET
SET TARGET.COL1 = (SELECT SOURCE.COL1 FROM SOURCE WHERE TARGET.KEY = SOURCE.KEY)

Ties Blom
Information analyst