how to use Table_Comparison Transform
Use of Table Comparison Transform
Introduction:–
Table_Comparison transform helps to compare two data sets and generates the difference between them as a resultant data set with rows flagged as INSERT, UPDATE, or DELETE.
This transform can be used to ensure rows are not duplicated in a target table, or to compare the changed records of a data warehouse dimension table.
It helps to detect and forward all changes or the latest ones that have occurred since the last time the comparison table was updated.
We will be using this transform frequently while implementing slowing changing dimensions and while designing dataflow for recovery.
Scenario:-
We are doing a scenario where we are transferring a data from one database table to another.
Target table contains previously loaded data, at the source table 2 new records are added & other records are updated , now we want to insert the new records & update the existing records based on conditions.
1) Create project, job, workflow & dataflow as usual.
2) Drag a source table to dataflow. Its contents are as follows.
3)Drag a target table to dataflow. Its contents are as follows.
5) Double click on Query & do the mapping.
6)Double click on Table_Comparison & provide the details as shown below.
- new entries 6 & 7 got added in the target table
- At 5 the row Country got changed from INDIA to IND.
- At 3 row LNAME got changed to Jone & CITY got changed to NewYork.
- In Source table at row 3 FNAME was changed to Janeee but it didn’t get reflected in the target table as we have have kept LNAME, CITY & COUNTRY as the options for comparing the tables.
By this way you can use table comparison depending upon your requirement.
Nice peace, straight forward and clear.good job
I really like your post. But I would like to know if is possible to use a vachar field instead number, interger whatever because I am getting a error related with this.
if not what could be a work around.
Hi Douglas,
Thanks for the comment.
Can you please elaborate your point?
Thanks & Regards,
Rahul More
What is the error you are getting?
Hi Arjun,
I am having the same problem with Douglas Ricardo.
The primary key I am using is a Varchar(32) type. Please see image below
The message is as following as the image
How do we use the table comparison when we have non-numeric primary key?
If you don't select the CIUNID as Generated key column, the update from the source to the target will not happen.
I would be great if you can shed some light.
Many thanks in advance
Ray
use Key_generation transform to generate a key ..and use that as a key column for comparison,
was recommended practice by sap.
That part is a wrong conclusion: “If you don’t select the CIUNID as Generated key column, the update from the source to the target will not happen.”
You have specified the CIUNID as the primary key column in TC, hence TableComparison will check if such a row exists in the compare table (logically speaking: “select * from NFDA_CLAIMITEMS where CIUNID = :1”), find a row and output the row as Update record.
The problem is your target table. The target table does receive a row of type update and will execute an “update NFDA_CLAIMITEMS set … where ????”. What should be the where clause? The where clause is based on the primary key information the target table has and it does not have any. That is the root cause. The target table has to have a primary key for updates.
When you validate (menu item) your dataflow it will warn you about that and tell that its only chance is to use all columns as a primary key. Every single column of the table is in the where clause. That will obviously never change a record.
Please make sure the target table has a physical primary key and the reimport it so that it is reflected in the Designer. Everything else is a hack with side effects.
If you are saying the CIUNID is not a primary key, then we need to talk again.
Hello Rahul,
It seems that the table comparison feature in BODS only works when the generated key column is a number!
Here's the error message I got:
Karim,
Where is the error message?
Thanks Rahul ,
Very clear explanation.
Hi Rahul ,
I have a small question , When using a Table Comparison transformation, if i have selected a Input primary key columns that is something other than a REAL, FLOAT, DOUBLE, INT, DECIMAL, OR NUMERIC (i.e) I have used VARCHAR, in that case I can change table compare Input primary key columns to either one of the listed types.
Do we have any alternate solution other than changing the type of table comparision input primary column ??
I am getting below error in table comparison. Please can you help.
Hi Swati,
Your mapping seems to be incorrect,
Please post the Query Transform scheenshot(if using) or check if you have passed required field into Table_comparision Transform.
Regards,
Shiva Sahu
Hi Rahul,
I am unable to update the rows, job is being executed successfully, but when i am changing the source table and executing the job. the target table is not being changed.
In the options of Target table, i have enabled "use Input Keys" also..Can u help me please
Regards,
Santhosh.
Hi,
Are you used map_operation transform?
If not, use map_operation trans.
Do the configuration in the map_operation trans:
Normal: delete
Insert: insert
Update: insert
Delete: delete
and check now
Hi Srinivas,
I have tried that also srinivas, but still not able to change the target data.
Very intuitive post sir.
Very helpful post Rahul. Thanks
Hi Rahul,
I'm trying to update a table but with information of an Excel. I've used your tutorial but I get the next error:
I have no idea what went wrong, would you help me, please?
I think this is due to access issues or key violation
I do 😉 .
Execute SET IDENTITY_INSERT Comites ON (you can do that directly in SQL Server or include the statement in the Pre-Load Commands tab of the target table editor).
Then try again.
Thanks, I've already executed that query but still having the same problem. In the specification appears that the Identity is already setted to on
Now I use a template table where I load the data of the Excel and in a second DataFlow I try to use the Table Comparision but still having the same problem
Hi
Have anyone got the solution for this, because me too , stuck here with the same problem. The target table is not getting updated!!!
Great Article Rahul!
But I would like to accomplish a Table Compare in an HCI Data Flow using Data Services but Table Compare does not seem to be available.
Any help in this would be great.
RS
Nice explanation. Thanks Rahul.
Its great for BODS but its different when you do it in HCI. using SAP Data Services. RS
Hello Rahul
In Source table at row 3 FNAME was changed to Janeee and it should also get changed in Target table as Table Comparison will check for all the records which have changed in Source and whatever changes are made in Source will be reflected in Target.
Compare columns section is used only to improve performance.
Please correct if my understanding is wrong.