Using multiple CTEs
I used this in SSIS as a script in DFT to get away from creating and writing temp tables.
;WITH cte1
AS
( SELECT field1,field2 FROM table1)
,
cte2
AS
( SELECT field1,field2 FROM table2)
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2
