​Use of Compare Columns in table comparison

[former_member593418]

I have one data flow as below source table -----> query transform ------> table comparison ----> target table

The columns present in the source table, with below data:

outlet_id, outlet_manager, outlet_city

===================================

M102345, Alan SeanYY, AustinXX

M102346, Jan Scott, Princeton

M102347, Santiago Oxford, Orlando

the columns present in the target table, with below data:

outlet_id, outlet_manager, outlet_city, created_date, last_updated_date ==================================================================MM102345, Alan Sean, Austin, 01/10/2018, 02/10/2018

M102346, Jan Scott, Princeton, 03/10/2018, 04/10/2018

Table comparison transform is set as
a) "Input primary key columns" = outlet_id
b) "compare columns" = outlet_manager, AND outlet_city

Question:
1) Since outlet_id M102347 is not existed in target_table, it will insert into target_table.
What can I do, so that created_date column is assigned with current date time as well when this data row is inserted in targe_table.

2) Since outlet_id M102345 is existed in target_table, it will update column outlet_manager and outlet_city in target_table.
What can I do, so that last_updated_date column is assigned with current date time, and created_date remained (not changed) when this data row is updated.

Regards,
Rock

Thanks.

[former_member187605 · Accepted solution]

Yes, if there are no timestamps in your input, you can pick up created_date from the target table before the table_comparison with a lookup or a left outer join. And assign sysdate() when null.

[former_member593418]

There is alternative way to resolve this problem, based on hint provided by Dirk in https://answers.sap.com/questions/680797/bods-how-to-update-some-columns-only-in-table.html.

Steps to implement this alternative way:

1) Add additional Map_Operation transform between Table_Comparison and Target_Table.

2) Go to tab "Mapping.

3) Click on column that should not be updated, then enter mapping expression "before_image(your_column_name)" in TextBox "Update Mapping".

4) Repeat step 4, for other columns that should not be updated.

[former_member593418]

Below diagram showed the data flow that is designed, based on advice from Dirk Venken.

[former_member593418]

Hey Dirk Venken...thanks for youradvice.

If created_date and last_updated_date column are added into source table inside Table_Comparison transform, and assigned these 2 columns with current datetime value, then it will update column outlet_manager, outlet_city, last_updated_date as well as column created_date in target_table (e.g. outlet_id M102345).

Column created_date should not be updated. Can it be done?

[former_member187605]

Make sure your input data stream has exactly the same structure before the Table_Comparison transform: you must add created_date and last_updated_date and assign them the current datetime value.