Adding a UNION to a Data Flow

How to:

Add a UNION to a Data Flow

Reference:

Union Types

Adding a union to a data flow lets you combine the results of two select statements into a single result set.

There are some restrictions on the select statements that can be used:

The two select statements must return the same number of columns.
The data type of each column in the first table must be the same as the data type of the corresponding column in the second table.
Neither of the select statements can include a sort (ORDER BY). However, the combined results may be sorted.

Procedure: How to Add a UNION to a Data Flow

From the data flow workspace:

Right-click to the left of a SQL object and click Add Union.

or

Right-click a SQL, Join, or Union object, point to Add, and then click Union.

or

Drag the Union object from the Insert group of the Flow tab, into the workspace.
A new Union object appears in the data flow. Right-click the object, select Add, and then click Union.
Continue by adding objects to the select as needed.
Right-click the Union object, point to Add, and then click Select (each union requires two select statements).
Continue by adding objects to the second select as needed.

Note: You can test the union by double-clicking the object and clicking the Test SQL tab to see a sample of the data.
Right-click the Union object and click Properties.
The Properties pane opens. Select the Union type. See Union Types for details.

Reference: Union Types

The Union object can be used to combine answer sets in different ways, depending on the type selected in the Properties pane. The different union types are:

Object	Function
	Union Returns a result that contains all rows from both sets with duplicate rows removed.
	Union All Returns a result that contains all rows from both sets, including duplicates.
	Except Returns a result set that contains all rows from the first set, excluding common rows from the second set.
	Except All Returns a result set that contains all rows from the first set, excluding common rows from the second set, including duplicates.
	Intersect Returns a result set that contains the common rows from two result sets.
	Intersect All Returns a result set that contains the common rows from two result sets, including duplicates.

Object

Function

Union Object