The easiest way I’ve found involves two steps:
Adjust SSMS Settings
- Go to Tools->Options
- Query Results->SQL Server->Results to Grid
- Check “Include column headers when copying or saving results”
- Click OK.
- Note that the new settings won’t affect any existing Query tabs — you’ll need to open new ones and/or restart SSMS.
Now next time you run a query, do this
- Make sure the results are displayed in a grid (CTRL+D or Query->Results To->Results to Grid)
- Right click in the grid, and click Select All
- Right click in the grid again & click Copy
- Open up a new Excel spreadsheet, and paste the data in
- Do a global search & replace, replacing “NULL” with an empty string.
I had tried before with SSMS’s export to CSV feature, and it just didn’t escape data the way I needed it to.