For example, if you have a table for every state, you can use
UNION ALL to concatenate all of them into a single table with values for the whole country.
UNION ALL WITH table1, table2, ...;
The schema of each table is carried into the unioned table; no attribute is dropped. If the initial schemas match, then the unioned table has the same exact schema. If there is an attribute in one schema that is different, then the unioned table includes it at the end of the schema.
Note: Matching attributes must have matching types. If the schemas have a matching attribute, the attribute must be the same type in each schema for them to be unioned correctly.
Important: Changing a
UNION ALLstatement can result in an unintentional change to
tamr_idvalues in the unified dataset. To avoid data loss and maintain the stability of
tamr_idin mastering and categorization projects, as a best practice Tamr recommends that you explicitly set the
tamr_idto a value you're sure is stable in the last transformation. See Managing Primary Keys. In addition, both the
origin_entity_idmust exist in the datasets being unioned or you must create and populate them with strings.
For example, you have Massachusetts and Vermont datasets containing resident information, and you want to union them.
To union these two tables together, you write a script such as:
USE Massachusetts.csv; UNION ALL WITH Vermont.csv
After the transformation runs, the new, unioned table contains:
Updated almost 2 years ago