Unite Component

Unite Component

Combine all the rows from two or more input flows into a single output flow. The input flows should be very similar for this component to be useful, although slight differences in the input schema can be accommodated by using the 'Cast Types' property.

Properties

Property Setting Description
Name Text The descriptive name for the component.
Method Select All Columns: All columns from all inputs are included in the output. Columns that do not exist in one of the input sources will have the SQL NULL value on any rows that come from that source.
Overlapping Columns: Only columns that appear in all input sources are included in the output. Columns that do not exist in all of the input sources are dropped.
Cast Types Select Yes: If the same-named column from multiple inputs have differing input types, attempt to cast them to a common type. This is not guaranteed to work, so you should check your data carefully.
No: If the same-named column from multiple inputs have differing input types, report an error and do not continue.
Add Source Component Column Select Yes: Add a column called "source_table" with the value of the input component name which provided each row of output.
No: Do not add an additional column to identify the input component name.
Remove Duplicates Select Yes: Remove (merge) duplicate rows so that only one of the duplicate rows remains and all resulting rows are unique. No: Do not remove duplicate rows. Allow duplicate rows to exist in the output table.

Strategy

Generates multiple select query separated by UNION ALL.

Example

In this example, we have a table of cases from the past month and we want to add it to a master table that is a compiling of all months' cases. The master list, in this way, is a kind of historical backup of all cases. To perform this task, we use the Unite component to append one table to the other. The job is shown below.

To begin, we note the row counts of the two input tables, 11,210,931 and 3,376.

Both of these tables feed into the Unite component that is configured as shown below.

We don't want to remove duplicates since each case has a unique ID to distinguish it and we don't care where the data is from so we don't opt to add a source component column. It is, however, beneficial to cast types in case a mistake has been made with the last month's table and its types are not correct to the master list version.

After going through the Unite component, the data is appended and the row count reports the sum of both input tables.