What is difference between 'Merge Data table' and 'Join data table' activity?

Help Studio
, ,
1

What is difference between ‘Merge Data table’ and ‘Join data table’ activity?

2

merge datatable is used e.g. to copy the datatable from a second datatable to distination datatable

1 Like
3

@ppr Thanks for your input. I am just trying to understand what could be possible difference between these 2 activities ? when to which specific activity?
Actually this question is being asked in interview repeatedly so seeking for an answer?

4

in that case the best would you implement two cases so you will understand and will get practical experience.

5

Thanks @ppr.
Actually I implemented both the scenarios and I am able to conclude above is the difference as per explanation.

I am still not clear about exact purpose of these activities when to use which one?
In real time scenario, based on what parameters we can decide which activity to use ?
Please help.

6

With Merge Datatable you can not give conditions,
You just merge a table into an other table.
It will use ALL records from BOTH table, it won’t “recognize” for you if it is the same record.
The matching columns will be present once only, but the distinct columns will be there too from both table.
If a column value is missing from one table then the field will be filled by “null” value.

For example:

dt1
[id,name,salary
1,Peter,100
2,Sam,150]

dt2
[id,name,email
1,Peter,peter@peterdotcom
3,John,john@johndotcom
]

Merge dt1 into dt2, you get:

[id,name,salary,email
1,Peter,100,null
2,Sam,150,null
1,Peter,null,peter@peterdotcom
3,John,null,john@johndotcom
]

For example Peter’s email: dt1.Rows(0).Item(“email”).GetType => [System.DBNull]

With Join DataTable, first of all you can do different type joins
(inner, left, full). Similarly to an SQL query.

INNER join basically is to get records that a present in BOTH table "join tables by a common field… like an ID or Passport number, or something.

LEFT join has all records from the first table, and adds more records from 2nd table with the condition.

Finally, FULL will get records from both table (like merge), but uses the condition to match the record from the two table, so you don’t get duplicates.

So, you also need to give some conditions, I give example
(using the same tables from above dt1, dt2)

With Inner Join you, if you condition “id=id” you get this as result

(since Peter is the only guy present in both table):

[id,name,salary,id_1,name_1,email
1,Peter,100,1,Peter,peter@peterdotcom
]

LEFT join with condition (id=id) you would get this:
(all records from 1st table, and Petere’s email will be added, the missing data will be ‘null’)

[id,name,salary,id_1,name_1,email
1,Peter,100,1,Peter,peter@peterdotcom
2,Sam,150,null,null,null
]

FULL join which will match the records by your condition (again lets use id=id), you will get this as result:

[id,name,salary,id_1,name_1,email
1,Peter,100,1,Peter,peter@peterdotcom
2,Sam,150,null,null,null
null,null,null,3,John,john@johndotcom
]

So join will get all columns once, from both table.

This is almost like an SQL expression, but not as configurable.
You can remove duplicates yourself if you want (Remove column) after to make it “look” like an SQL result but for example in my example you need to make sure the null values are filled up, (e.g. John’s id shold be updated with id_1)…

or you can play around with it…

If you learn a bit of SQL (there are few short few hour videos of basics of SQL on youtube) you will understand these activities better, as they are basically same as SQL an query made into “UiPath Activity” with a “wizard” button

Of course I shown ‘extreme’ examples, where the two table is different (one has salary the other one email). If the two table is ‘matching’ (normally happens when we try merge two excel file or something) then the result is less ‘frigthening’. You can then just probably need to ‘remove’ duplicates which is kind of easy.

2 Likes
7

Maybe this example helps too this time I used dt1 and dt2 with the same columns:

dt1:
[id,name,salary
1,Peter,100
2,Sam,150
]

dt2:
[id,name,salary
1,Peter,200
3,John,300
]

Merge will result like this (4 record, 2 from each table):
[id,name,salary
1,Peter,200
3,John,300
1,Peter,100
2,Sam,150
]

Join (FULL) with condition id=id will result this
(3 records Peter’s id is same in both table):

[id,name,salary,id_1,name_1,salary_1
1,Peter,100,1,Peter,200
2,Sam,150,null,null,null
null,null,null,3,John,300
]

Join (Inner) with condition id=id will give 1 record (Peters)

[id,name,salary,id_1,name_1,salary_1
1,Peter,100,1,Peter,200
]

and Join left gives 2 records (2 from left table + peter’s data from right table but on the same record) missing data filled with null again (for Sam)

[id,name,salary,id_1,name_1,salary_1
1,Peter,100,1,Peter,200
2,Sam,150,null,null,null
]

When you join all fields will be both present in the new table as “fieldname” and “fieldname_1”, when you merge the matching columnnames will be there only once, but records will be there from both table. Briefly.

2 Likes