Friday, July 10, 2009

Quick SQL --> Excel export w/ column names

I know there's lot of ways to do this, including an SSIS/DTS package, but here's another quick way I pieced together for ad-hoc exports with that I haven't seen outlined before:

1. Run the query in SQL Server Management Studio (w/ "Results to Grid")
2. Copy all data from the grid (right-click on the unlabled box at the top left of the grid, choose "copy"), and paste into second row in Excel.
3. Back in SQL, run this query:

SELECT sc.name
FROM syscolumns sc
INNER JOIN sysobjects so
ON so.id = sc.id
WHERE so.name = '[table name]'
ORDER BY sc.colid

4. Paste the results into Excel in a new sheet.
5. Copy the row, then "paste special" --> "transpose" into the first row in the original worksheet, so that they paste as columns.

This will probably break down for very large tables, but I just did this with 96 columns, and 10,000+ rows without issues.

Some more ways to accomplish this:
http://www.mssqltips.com/tip.asp?tip=1202

BTW, not sure why there's not a "Results to .csv format" in SQL Server Management Studio. "Results to Text" seems rather useless...

UPDATE: Dave Kearns points out an even easier way to do this:

In SQL Server Management Studio, go to "Tools" --> "Options" --> "Query Results" --> "SQL Server" --> "Results to Grid" --> check "Include column headers when copying or saving the results"