Export query result to .csv file in SQL Server 2008

Question

1. Open SQL Server Management Studio
2. Go to Tools > Options > Query Results > SQL Server > Results To Text
3. On the far right, there is a drop down box called Output Format
4. Choose Comma Delimited and click OK

Here's a full screen version of that image, below

This will show your query results as comma-delimited text.

To save the results of a query to a file: Ctrl + Shift + F

I know this is a bit old, but here is a much easier way...

1. Run your query with default settings (puts results in grid format, if your's is not in grid format, see below)

2. Right click on grid results and click "Save Results As" and save it.

If your results are not in grid format, right click where you write the query, hover "Results To" and click "Results To Grid"

Be aware you do NOT capture the column headers!

Good Luck!

You can use PowerShell

$AttachmentPath = "CV File location"
$QueryFmt= "Query"

Invoke-Sqlcmd -ServerInstance Server -Database DBName -Query $QueryFmt | Export-CSV $AttachmentPath

If the database in question is local, the following is probably the most robust way to export a query result to a CSV file (that is, giving you the most control).

1. Copy the query.
2. In Object Explorer right-click on the database in question.
3. Select "Tasks" >> "Export Data..."
4. Configure your datasource, and click "Next".
5. Choose "Flat File" or "Microsoft Excel" as destination.
6. Specify a file path.
7. If working with a flat file, configure as desired. If working with Microsoft Excel, select "Excel 2007" (previous versions have a row limit at 64k)
8. Select "Write a query to specify the data to transfer"
9. Paste query from Step 1.
10. Click next >> review mappings >> click next >> select "run immediately" >> click "finish" twice.

After going through this process exhaustively, I found the following to be the best option

PowerShell Script

$dbname = "**YOUR_DB_NAME_WITHOUT_STARS**"
$AttachmentPath = "c:\\export.csv"
$QueryFmt= @"
**YOUR_QUERY_WITHOUT_STARS**
"@

Invoke-Sqlcmd   -ServerInstance **SERVER_NAME_WITHOUT_STARS** -Database  $dbname -Query $QueryFmt | Export-CSV $AttachmentPath -NoTypeInformation

Run PowerShell as Admin

& "c:\path_to_your_ps1_file.ps1"

Use T-SQL:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [FileName.csv]')
SELECT Field1, Field2, Field3 FROM DatabaseName

But, there are a couple of caveats:

1. You need to have the Microsoft.ACE.OLEDB.12.0 provider available. The Jet 4.0 provider will work, too, but it's ancient, so I used this one instead.

2. The .CSV file will have to exist already. If you're using headers (HDR=YES), make sure the first line of the .CSV file is a delimited list of all the fields.

MS Excel -> Data -> New Query -> From Database ..follow the steps