Skip to Content
Author's profile photo Former Member

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.

Assigned Tags

      27 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Nice peace, straight forward and clear.good job

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Douglas,

      Thanks for the comment.

      Can you please elaborate your point?

      Thanks & Regards,

      Rahul More

      Author's profile photo Arjun Rao
      Arjun Rao

      What is the error you are getting?

      Author's profile photo Former Member
      Former Member

      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

      DS Table Compare.png

      The message is as following as the image

      DS Table Compare Error Message.jpg

      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

      Author's profile photo A Reddy
      A Reddy

      use Key_generation transform to generate a key ..and use that as a key column for comparison,

      was recommended practice by sap.

       

      Author's profile photo Werner Dähn
      Werner Dähn

      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.

      Author's profile photo Former Member
      Former Member

      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:

      Author's profile photo Arjun Rao
      Arjun Rao

      Karim,

      Where is the error message?

      Author's profile photo david king
      david king

      Thanks Rahul ,

           Very clear explanation.

      Author's profile photo david king
      david king

      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 ??

      Author's profile photo Former Member
      Former Member

      I am getting below error in table comparison. Please can you help.

      table comparison error.JPG

      Author's profile photo shiva sahu
      shiva sahu

      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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      Hi Srinivas,

      I have tried that also srinivas, but still not able to change the target data.

      Author's profile photo Former Member
      Former Member

      Very intuitive post sir.

      Author's profile photo Azhar Uddin
      Azhar Uddin

      Very helpful post Rahul. Thanks

      Author's profile photo Former Member
      Former Member

      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: /wp-content/uploads/2016/04/pantalla_929486.png

      I have no idea what went wrong, would you help me, please?

      Author's profile photo Pravi Nellikkal
      Pravi Nellikkal

      I think this is due to access issues or key violation

      Author's profile photo Dirk Venken
      Dirk Venken

      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.

      Author's profile photo Former Member
      Former Member

      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

      /wp-content/uploads/2016/04/pantalla_930169.png

      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

      Author's profile photo Moumita Maity
      Moumita Maity

      Hi

        Have anyone got the solution for this, because me too , stuck here with the same problem. The target table is not getting updated!!!

      Author's profile photo RS sharma
      RS sharma

      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

      Author's profile photo Ravi kiran Pagidi
      Ravi kiran Pagidi

      Nice explanation. Thanks Rahul.

      Author's profile photo RS sharma
      RS sharma

      Its great for BODS but its different when you do it in HCI. using SAP Data Services. RS

      Author's profile photo Rajan Burad
      Rajan Burad

      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.