or how to place a query result (with the heading of the columns) into Excel spreadsheet.

"I can accept failure, but I can't accept not trying." - Michael Jordan

How many times did you read "In Query Analyzer, there was no way to copy the heading of the columns from a Result Set, if the output was in grid"? Or you could read long and not working solution for this simple task.

Now, here are two methods, step by step, how to place a query result into Excel spreadsheet:

Method 1

Highlight, drag and drop a result set from the Results Pane into Excel spreadsheet. But wait - there is a little trick you should know before you can make this technique working.

1. Open an Excel spreadsheet where you will place the results.

Now, in the SQL Query Analyzer, we need to get Results in text!

2. Click Query from Menu bar

3. Click Results in Text

Make sure that you have “Tab delimited” option in Results Output Format

dialog box!!!

4. Click Tools

5. Click Options

6. Click Results

7. Select “Tab delimited” option in “Results Output Format” window!!!!!

Once you setup “Tab delimited” option in Results Output Format dialog box you could keep it unchanged.

8. Click "Print column headers(*)".

9. Click OK.

The next step is to get select statement using the Object Browser. You should use Object Browser

to get all tables and column names.

10. Right click on the table Customers in database Northwind.

11. Click Script Object to New Window As

12. Click Select. Now you will have a select statement in the Editor Pane. You may modify it. If you need to write join you can save select statement generated by Object Browser to clipboard and paste it to current open Editor Pane.

13. Press P5 to run your query

14. Click anywhere in the Results Pane

15. Press CTRL + A to highlight the result set

16. Drag and drop the result into Excel spreadsheet

Using this technique you can get into Excel spreadsheet the output of multiple queries in one shot. (For queries that you run on regular basis you should use a DTS package.)

Here are some extra steps you should perform in Excel spreadsheet:

17. Click 1 to highlight the first row – the header row

18. Click B (bolt) button

19. Click select all button to select the content of spreadsheet

20. Click Format from Menu bar

21. Click Column

22. Click AutoFit Selection

23. Save Excel spreadsheet

You may need to remove the number of rows affected message text from the bottom of the Excel spreadsheet.

(Although this process seems to be lengthy it should take few minutes to complete it first time! And after you master it – few seconds.)

Method 2

This simple method should save you time when you present your results in Excel spreadsheets. The SQL Query Analyzer can directly save the results in Excel spreadsheet. That means you do not need to copy and paste or drag and drop the results - The SQL Query Analyzer does it for you and all it does is for free.

1. Click Tools from the Menu bar

2. Click Options

3. In Options dialog box click Results tab

4. Select “Tab delimited” option in “Results Output Format” window

5. Select “Print column headers(*)”

6. Click OK

7. Click Query from the Menu bar

8. Click Results to File (later you can use the shortcut CTRL+ SHIFT+F)

9. Type in your query in Editor Pane or use the Object Browser to generate a query

10. Press F5 key to execute your query

11. You should get the Save Results dialog box

12. In File Name window type in the name of your output file. For example: Orders.xls. Make sure you provided the file extension (xls).

13. Click Save.

In the Results Pane you will get the number of rows in the result set, path to the output file and the size of the file.

14. Now, you can open your file using Windows Explorer.

15. Click 1 to highlight the first row – the header row

16. Click B (bolt) button.

17. Click Save.

If asked "Do you want to keep the format of the file" click No.

You can save your results using CSV (comma separated value) file format if no “comma” appears in any column. You can also save your results using “Custom Delimiter” option. In this case you may use Actual Tilda character “~” or pipe “|” as a delimiter.

If you need to schedule your query and place results into Excel spreadsheet you can find a solution in an excellent article "Using DTS to Generate and Email Excel Reports" by Joseph Sack.

Credits: to all my colleagues for their unique sense of humor and endless jokes. This keeps my creativity engine working.