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.