UNION ALL: Combining Data from Multiple Sources - Parallel Transporter

UNION ALL: Combining Data from Multiple Sources

UNION ALL takes data from two or more sources, obtained by different producer operators working in parallel, and combines the output data rows into a single logical data stream that is applied to the desired data target(s). Since all of the operators involved in a UNION ALL operation are working in parallel, the time to acquire source data is significantly reduced.

The following producer operators are typically used with UNION ALL:

DataConnector

ODBC

Usage Requirements

To be compatible with UNION ALL one of the following must be true:

The rows put onto the output data streams by all UNION ALL producer operators must be identical in column structure. This is the case if the producer schemas are all UNION-compatible (see “Using Multiple Source Schemas” on page 50).

or,

A similar result can be achieved through column selection and/or the use of derived columns in the SELECT clauses that are combined with UNION ALL and the APPLY statement, even if all producer schemas are not UNION-compatible.

Code Example

SELECT * FROM OPERATOR (REGION_1_ACCOUNTS_READER) 

UNION ALL 

SELECT * FROM OPERATOR (REGION_2_ACCOUNTS_READER) 

UNION ALL is used in a number of common job scenarios. For a typical application Example 1C in “Job Example 1: High Speed Bulk Loading into an Empty Table” on page 103.

For the sample script that corresponds to this job, see the following script in the sample/userguide directory

PTS00003: High Speed Bulk Loading from Two Flat Files to an Empty Teradata Database Using UNION ALL.