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