UNION ALL

You can combine multiple tables and queries together by using UNION ALL.

Note: UNION will parse, but it will be treated as UNION ALL –ie. duplicate records kept in the returned data set.

Example:

SELECT * FROM table1 UNION ALL SELECT * FROM table2

Example, using the UNION task from the InMemory ETL tool:

Source data, CSVdataC.txt:

Source data, CSVdataD.txt:

The InMemory project:

Resulting script from InMemory project:

LOAD ASSEMBLY 'TARGIT.Csv.dll' //server
DATASOURCE [CSV data source] = DOTNET CONNECTION 'TARGIT.CSV.CsvConnection' 'HasHeaders=true;loadasstring=false;type=local;delimiter=,;encoding=Windows-1252;culture=en-US;detectionrowscount=100;skiplines=0;'
IMPORT [tmp_CSV_C] = [CSV data source].{SELECT * FROM [C:\Temp\CSVdata\CSVdataC.txt]}
IMPORT [tmp_CSV_D] = [CSV data source].{SELECT * FROM [C:\Temp\CSVdata\CSVdataD.txt]}
IMPORT [tmp_CSV_C_Type] = [ME].{SELECT *, 'C' as [Type] FROM [tmp_CSV_C]}
IMPORT [tmp_CSV_D_Type] = [ME].{SELECT *, 'D' as [Type] FROM [tmp_CSV_D]}
IMPORT [CSV_Union]=
[ME].[tmp_CSV_C_Type]
 UNION ALL 
[ME].[tmp_CSV_D_Type]
SAVE

Resulting CSV_Union table: