CODEWITHSUNDEEP
informaticainformatica-powercentercdcoracle-cdc
Sudesh Elvin Rego
Sudesh Elvin Rego

Reputation: 23

Informatica : something like CDC without adding any column in target table

I have a source table named A in oracle. Initially Table A is loaded(copied) into table B next I operate DML on Table A like Insert , Delete , Update .

How do we reflect it in table B ? without creating any extra column in target table. Time stamp for the row is not available.

I have to compare the rows in source and target

eg : if a row is deleted in source then it should be deleted in target. if a row is updated then update in target and if not available in source then insert it in the target .

Please help !!

Upvotes: 1

Views: 470

Answers (2)

Amit
Amit

Reputation: 1

If you do not want to retain the operations done in target table (as no extra column is allowed), the fastest way would simply be - 1) Truncate B 2) Insert A into B

Upvotes: 0

Samik
Samik

Reputation: 3455

Take A and B as source.

Do a full outer join using a joiner (or if both tables are in the same databse, you can join in Source Qualifier)

In a expression create a flag based on the following scenarios.

  1. A key fields are null => flag='Delete',
  2. B key fields are null => flag='Insert',
  3. Both A and B key fields are present - Compare non-key fields of A and B, if any of the fields are not equal set flag to 'Update' else 'No Change'

Now you can send the records to target(B) after applying the appropriate function using Update Strategy

Upvotes: 1

Related Questions