Skip to Content

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.

To report this post you need to login first.

25 Comments

You must be Logged on to comment or reply to a post.

  1. Douglas Ricardo

    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.

    (0) 
      1. Raymond Lin

        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

        (0) 
  2. karim attaleb

    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:

    (0) 
  3. 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 ??

    (0) 
    1. 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

      (0) 
  4. Santhosh Sreshta

    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.

    (0) 
    1. Srinivas Bandi

      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

      (0) 
  5. Daniel Ramirez

    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?

    (0) 
    1. 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.

      (0) 
      1. Daniel Ramirez

        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

        (0) 
        1. 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!!!

          (0) 
  6. 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

    (0) 
  7. 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.

     

    (0) 

Leave a Reply