On occassion, I need to take a number of different result sets and combine them into a single output, which I then want to store in a temporary table. I’ve done this while developing solutions for business needs and when analyzing performance for different environments. Sometimes I just need to store stuff.

What makes this interesting is when I am using UNION to join the results. How do you place a final resultset from a UNION, EXCEPT, or INTERSECT into a temporary table using SELECT INTO? Where does the INTO portion of the query go?

This is actually a pretty simple thing to do. The INTO for the SELECT INTO goes into the first query of the set. An example of UNIONing the results from sys.dm_exec_query_stats and sys.dm_exec_query_stats into a temporary table is provided in listing 1.

--Listing 1. Query to UNION results into temporary table.
SELECT plan_handle, execution_count, total_elapsed_time
INTO #stats
FROM sys.dm_exec_query_stats
UNION ALL
SELECT plan_handle, execution_count, total_elapsed_time
FROM sys.dm_exec_query_stats

Creating temporary tables in this fashion is easy and simple. I mention how to do this because, I forgot for longer than I want to admit that this can be accomplished without a sub-query.