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.
