Performing a union all

A UNION ALL in SQL combines the rows from one table with the rows from another, pending both tables contain the same number of columns with matching data types, similar to a merge in 1010data. With this variation of UNION all rows, including duplicates, are retained in the final worksheet.

Difficulty

Objective

You want to combine the rows of two tables that contain weather observations for different years. Additionally, if there are duplicate rows between the two tables, you want to retain both copies. You know how to do this in SQL by performing a UNION ALLwith the two tables, and you would like to obtain the same results using 1010data.

SQL solution

SELECT * 
FROM hourly_weather_90
UNION ALL
SELECT * 
FROM hourly_weather_91;

1010data Macro Language solution

<base table="pub.demo.weather.hourly90"/>
<merge table2="pub.demo.weather.hourly91" type="all"/>

With SQL, UNION ALL will combine all rows from both tables and will retain duplicates. To obtain the same worksheet with 1010data, you can specify type="union" in the <merge> operation. However, the default behaviour for 1010data is to retain all rows, so not specifying a type will produce the same results.

Further reading

If you would like to learn more about the functions and operations discussed in this recipe, click on the links below:

<merge>