Sometimes when you have a query result, you find that you want to make further analysis on the result. Maybe resort it, maybe filter it further, or just save it for later. Today, you can easily copy the data to Excel, but a DBA does not want data in Excel, he wants it in a database! So why not provide an option to save the result set in a table? "So what, just rerun the query, and this time add an INTO or INSERT clause?", I can hear the skeptic Program Manager from Microsoft say. But there are plenty of situations where is not feasible:
1) The query takes a long time to run, and causes a load on a production system and it is not permissible to rerun it.
2) The data may come from a DMV and includes a momentary situation, for instance a blocking scenario, which cannot be reproduced.
3) The result comes from a stored procedure which produces many columns, and composing the CREATE TABLE statement for the table is a major headache.
4) You want to save the result on a different server for whatever reason.
5) Drum roll! The result may not exist on a single server, because it is the result ot a multi-server query.
Kudos to SQL Server MVP Arnie Rowland for giving me the inspiration.