Table Comparison Transformation in SAP BODS
Table Comparison Transformation
Table comparison transformation is used to compare the two data sets and check what are the records were changed and updates, inserted and deleted.
Comparing two data sets, this transform can generate the difference between them as a resultant dataset with each row of the result flagged as insert, update, or delete.
While loading data to a target table, this transform can be used to ensure a row are not duplicated in a target table and hence is very helpful to load a dimension table.
The transform will take in records from a query- compare them with the target table.
First is will identify incoming records that match target records based on the key columns you select.
Any records that do not match will come out of the transform as inserts.
Records that match on the key columns will then be compared based on the selected compare columns.
Records that match exactly on the key and compare columns will be ignored – i.e. not output by the table compare transform.
Records that match on the key columns but differ on the compare columns will be output as update records.
The use of table comparison is used to update, insert and delete the records.
Now in the figure 1 the source data for FIRST_NAME column we are modifying the record ‘TOM’ as ‘TOMY’ so this record needs to be updated which means in the place of ‘TOM’ the record ’TOMY’ must be placed and now we are inserting new row that has to inserted after the row number 3.Now we can see by looking at the below target table
Figure 1: Sample Source Data
In figure 2 we can observe that the updated inserted records are in target table
Figure 2: Sample Target Data
By analyzing the above table the updated record in first row and inserted records in fourth row.
what are the changes happened in source file that need to be reflected in target table ,which means updated, Inserted, deleted records must be known
NOTE: – In table comparison transformation we can’t use generated key column as VARCHAR it always must be INT
Below figure 3 indicates the flow of table comparison transformation hierarchy
We can see the source data in below figure 5
we can see the settings for table comparison transformation as shown in figure 6.if you want to detect the deleted record you have to enable the “ DETECT DELETED ROW(S) FROM COMPARISON TABLE” and if you want to allow the duplicates for primary key you have to enable the ”INPUT CONTAINS THE DUPLICATES KEYS”
The inserted ,updated and deleted records indicates as” I” and “U” and “D” flags .This flags will be seen in debug mode only.