16.20 - UNION ALL: Combining Data from Multiple Sources - Parallel Transporter

Teradata® Parallel Transporter User Guide

Product
Parallel Transporter
Release Number
16.20
Published
August 2020
Language
English (United States)
Last Update
2020-08-27
dita:mapPath
uah1527114222342.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.ditaval

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

    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.

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.