5.7 Saving Query Results

With the Query Analyzer's Save Results feature, results of a statement execution can be saved to a variety of different file formats or copied to the clipboard for reuse or pasting elsewhere. The Save Results dialog can be launched by right clicking within a Query Results window and selecting "Save Results" from the popup menu. It can also be launched by clicking the Save Results icon, a floppy disk with a grid overlay, in the Query Analyzer Toolbar to the left of the Print icon.

The Save Results allows a wide variety of formats including, Delimited Data, INSERT statements, XML, HTML, and Excel worksheets. Selected Results can also be sent directly to the clipboard by clicking the "Save To Clipboard" button in the Save Results dialog. (Data Format for Excel does not save to clipboard).

For cases where large results sets (100,000 rows or more) are being dealt with, it is best to use Save Results instead of Save To Clipboard. Copying a large result set to the clipboard dramatically increases the RAM in use. Save Results streams data directly to disk and it is highly recommended that "Save Results" or Export be used instead of copying large amounts of data from a Grid Result.

Using the Operating System's clipboard for a large copy effectively doubles the amount of RAM in use because the Grid Result is already holding those items in memory. Saving to a file uses much less RAM, as a file is streamed to disk instead of being kept whole in memory.

Below are the steps for Save Results and a list of all of its options and settings.

1. File - Specify the file that will receive the results
2. Encoding - Indicate the character encoding
3. Platform - Select either Windows or Unix platform (to choose the end-of-line delimiter)
4. Results - Choose the panel containing the content to be saved from
   • Grid Results -
   • Pivot Grid Results -
   • Text Results -
   • Client Statistics -
   • Explain Plan -
5. Data Format - Select the format of the data to be saved
   • Delimited Data - save the results as a series of delimited rows of data
     • Delimiter - character used to delimit columns of data
     • String quoted identifier - character used to delimit strings
     • Include columns as first row - Include column names as the first row of the document
     • Include row count as first column - include row number in the first column of each row
     • Set text to (null) on NULL value - use the string "(null)" to represent a NULL value in the document
   • INSERT Statements - save the results as a series of INSERT SQL statements
     • Schema Name - specify the name of the schema containing the table
     • Table Name - specify the table name
     • Statement Separator - select a statement separator
     • Include table CREATE as first statement - include a CREATE SQL statement as the first statement of the document
   • XML Document - save the results as an XML document
     • String quoted identifier - character used to delimit strings
     • Include columns as first row - include column names as the first row of the document
     • Include row count as first column - include row number in the first column of each row
     • Set text to (null) on NULL value - use the string "(null)" to represent a NULL value in the document
   • HTML Document - save the results as a formatted HTML document
     • Include SQL Statement - include the SQL statement in the document
     • String quoted identifier - character used to delimit strings
     • Include columns as first row - include column names as the first row of the document
     • Include row count as first column - include row number in the first column of each row
     • Set text to (null) on NULL value - use the string "(null)" to represent a NULL value in the document
   • Excel Worksheet - save the results as a Microsoft Excel spreadsheet
     Note: this format cannot be sent to the Clipboard
     • Include SQL Statement - include the SQL statement in the document
     • Include columns as first row - include column names as the first row of the document
     • Include row count as first column - include row number in the first column of each row
     • Set text to (null) on NULL value - use the string "(null)" to represent a NULL value in the document
6. Click on the Preview Script Tab to see what Aqua Command script you may have set to run on the results before saving. For full information on Aqua Commands and scripting Aqua Data Studio features, see the Aqua Command section's sample scripts.
7. Click Save to Clipboard to send the results to the clipboard in the specified data format.
8. Click Save to File to send the results to a file in the specified data format.