Delete rows from target not present in source; Row by Row select

[rajan_burad]

Hello All,

What if on one fine day client asks to remove all the records which are present in target but not present in source or may be clients asks to remove at least one record from target and preserve the remaining? So to achieve this BODS provides a transform called Table Comparison and its feature Detect Deleted Rows from comparison table comes handy in achieving this task.

Let’s see how!

Consider my below source, targets and mapping to achieve the data load.

Source:



Mapping:







Target:



As of now Target table has all the records which are present in source which means synchronization is there.

Now what if the records are present in target but not present in source?

Have a look at the new source, mapping and target:

Source:



Mapping:



Target:



EMP_ID 1010 exists in target but isn’t in source. L Now my first requirement is to discard the latest record but keep the rest. So I run the job.

NOTE: Kindly ignore the update part as during experimenting the Update flags might create smoke and mirrors.

As you can see for the latest record with EMP_ID = 1010 and S_No = 5 transform generates a delete.

For 1010 the generated key column has highest value as 5. So this row should be deleted and rest all records should be preserved.

So my final target becomes:



Now what if I want to maintain sync between source and target. Since record with emp_id 1010 is absent in source but present in target so I want to remove those id’s from target as well.

Source:



Mapping: Checking Detect all rows option.



 

Target before execution:



Now I execute the job. For all the duplicates present in target but not present in source the transform generates Delete ‘D’ flag.



Hence my final table records are:



Target records are now completely in sync with source.

Hope it helps!

Please let me know if I've missed anything.

Thanks. 🙂