2

What's the easiest way to export data to excel from SQL Server 2000.

I want to do this from commands I can type into query analyzer.

I want the column names to appear in row 1.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
cindi
  • 4,571
  • 8
  • 31
  • 38

2 Answers2

6

In Query Analyzer, go to the Tools -> Options menu. On the Results tab, choose to send your output to a CSV file and select the "Print column headers" option. The CSV will open in Excel and you can then save it as a .XLS/.XLSX

alt text

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
3

Manual copy and paste is the only way to do exactly what you're asking. Query Analyzer can include the column names when you copy the results, but I think you may have to enable that somewhere in the options first (it's been a while since I used it).

Other alternatives are:

  1. Write your own script or program to convert a result set into a .CSV or .XLS file
  2. Use a DTS package to export to Excel
  3. Use bcp.exe (but it doesn't include column names, so you have to kludge it)
  4. Use a linked server to a blank Excel sheet and INSERT the data

Generally speaking, you cannot export data from MSSQL to a flat file using pure TSQL, because TSQL cannot manipulate anything outside the database (using a linked server is sort of cheating). So you usually need to use some sort of client application anyway, whether it's bcp.exe, dtswiz.exe or your own program.

And as a final comment, MSSQL 2000 is no longer supported (unless your company has an extended maintenance agreement) so you may want to look into upgrading at some point.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
  • The linked server was the way to go, I didn't rule out cheating in my question :-) – cindi Sep 23 '10 at 11:40
  • This answer is incorrect. In particular, these assertions are false: "Manual copy and paste is the only way to do exactly what you're asking... Other alternatives are: Write your own script or program to convert a result set into a .CSV or .XLS file ... Generally speaking, you cannot export data from MSSQL to a flat file using pure TSQL, because..." As Joe Stefanelli's answer below shows, Query Analyzer options can *easily* be changed to output query results to a .csv file. You do not, in fact, need your own script or "kludge" program to accomplish this. – PBoillot Dec 18 '18 at 19:57