Merge Activity

Use the Merge activity allows to join two sorted inputs. Both the left and right inputs must be sorted on a specified sort key before you can merge or join inputs. You must specify at least one sort key for both the left and right inputs. The Merge activity resolves any duplicates.

Use an XPath query to specify the recurring element for each input. There are six types of merges:
Merge Type Description
Merge and remove duplicates Removes the duplicates that occur in both the left and right inputs. Studio sends any duplicates to the RemainderXML element.
Merge and keep left duplicates Sends duplicates from the left input to the RemainderXML element.
Merge and keep all duplicates Keeps duplicates from both inputs.
Inner join Joins inputs on the specified keys. Any row that does not participate in the join is sent to the RemainderXML element.
Left outer join Joins the inputs on the specified keys. Rows from the left input are kept in the merged output even if they do not participate in the join. Studio sends any rows from the right input that do not participate in the join to the RemainderXML element.
Full outer join Joins the inputs on the specified keys. Rows that are not involved with the join are kept in the merged output.
Merge Examples:

Table 1 are the Left and Right inputs for this example. Table 2 are the expected results for each type of merge operation that the Merge activity supports.

In the following example, x is the key variable. Studio sends entries that are merged or joined to the mergeXML element and sends any entries that do not participate in a merge or join to the RemainderXml element.

Table 1. Left and Right Inputs
Left Inputs Right Inputs
<entries>
<entry x=”1” y=”10”/>
<entry x=”2” y=”20”/>
<entry x=”2” y=”21”/>
<entry x=”3” y=”30”/>
</entries>
<entries>
<entry x=”0” z=”00”/>
<entry x=”2” z=”22”/>
<entry x=”2” z=”23”/>
<entry x=”4” z=”40”/>
</entries>
Table 2. Merge Results for Each Merge Type
Merge Type Merge Results
Merge  and remove duplicates
<entries>
<entry x=”0” z=”00”/>
<entry x=”1” y=”10”/>
<entry x=”3” y=”30”/>
<entry x=”4” z=”40”/>
</entries>
Merge and keep left duplicates
<entries>
<entry x=”0” z=”00”/>
<entry x=”1” y=”10”/>
<entry x=”2” y=”20”/>
<entry x=”2” y=”21”/>
<entry x=”3” y=”30”/>
<entry x=”4” z=”40”/>
</entries>
Merge and keep all duplicates
<entries>
<entry x=”0” z=”00”/>
<entry x=”1” y=”10”/>
<entry x=”2” y=”20”/>
<entry x=”2” y=”21”/>
<entry x=”2” z=”22”/>
<entry x=”2” z=”23”/>
<entry x=”3” y=”30”/>
<entry x=”4” z=”40”/>
</entries>
Inner join
<entries>
<entry x=”2” y=”20” z=”22”/>
<entry x=”2” y=”20” z=”23”/>
<entry x=”2” y=”21” z=”22”/>
<entry x=”2” y=”21” z=”23”/>
</entries>
Left outer join

<entries>
<entry x=”1” y=”10”/>
<entry x=”2” y=”20” z=”22”/>
<entry x=”2” y=”20” z=”23”/>
<entry x=”2” y=”21” z=”22”/>
<entry x=”2” y=”21” z=”23”/>
<entry x=”3” y=”30”/>
</entries>
Full outer join
<entries>
<entry x=”0” z=”00”/>
<entry x=”1” y=”10”/>
<entry x=”2” y=”20” z=”22”/>
<entry x=”2” y=”20” z=”23”/>
<entry x=”2” y=”21” z=”22”/>
<entry x=”2” y=”21” z=”23”/>
<entry x=”3” y=”30”/>
<entry x=”4” z=”40”/>
</entries>