Add option to context menu in Results Grid: Save As Table

Add option to context menu in Results Grid: Save As Table by Erland Sommarskog

Closed

Type:	Suggestion
ID:	776369
Opened:	1/11/2013 12:35:10 PM
Access Restriction:	Public

Workaround(s)

view

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.

Details (expand)

English

Tools (SSMS, Agent, Profiler, Migration, etc.)

Add an alternative to the Context Menu in grid and, if possible, text mode: Save As Table that opens a dialog where you can specify a server and a database. For inspiration, look at Profiler which has precisely this feature.

I realise that for faithful saving, SSMS would have to save all data in native format, and not only text, and I assume that currently SSMS only has it in text form. I think it would be an OK restriction that data is saved from the text representation, as long as the table has the same data types as the original result set. One question how nameless columns will be handled; SSMS could prompt the user but suggest a default name, Coln, where n is the position in the result set.

I think that it would also be a good idea to add a column to the result set with the row number - some people will ask for it anyway.

Improved User Interface

I see this a primary a benefit for DBA that runs system queries or multiserver queries.

0 attachments

Totally support the request. The free SSMS Tools (NO LONGER FREE FOR SQL 2012, THOUGH!) lets us script as INSERTs, but that script still needs editing and with $0, after SQL 2012 we're dependent upon MS...

While MS is there, could we *please* have an _option_ to set the default "Save Results As" file type to tab-delimited _or_ csv, rather than the csv type we have to change every time...? It's VERY tedious when parallel running new vs. old to have to always change the file type... Same is true when needing to export for Excel consumption and cells contain commas...