Skip to Content

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.

4) Drag a Query & Table_Comparison transform to dataflow & connect them as shown below.

5) Double click on Query & do the mapping.

6)Double click on Table_Comparison & provide the details as shown below.

  • Table Name:- Select Target Table from the dropdown box.
  • Generated Key Column:- Specify key column
  • Select the “EMP_ID” node from the tree on LHS & drag into “Input primary key columns” list box. Now the comparison of the target table will take place based on whether the source EMP_ID is present in the target or not & comparison will be made based on the column s given under “Compare columns  list box.
  • Similarly select the columns that are to be compared while transferring the data & drag it to “Compare Columns” list box.
  • Select “Cached comparison table” radio button.

7) Save & Validate the job.

8) Execute the  job & check the output.

  • 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.

25 Comments
You must be Logged on to comment or reply to a post.
  • 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 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

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

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

    • 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

        /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

        • 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

  • 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.