Query to Output SQL table to Excel or CSV

I have been trying to export my table to a csv through a SQL query and no luck.

The attempt was using the OUTPUT function but I got an error. Below is the query:

SELECT * FROM [table];
OUTPUT TO 'C:foldernew_data.csv'
FORMAT TEXT
QUOTE '"'
WITH COLUMN NAMES;


This was the error:

Msg 102, Level 15, State 1, Line 81
Incorrect syntax near 'OUTPUT'.
Msg 319, Level 15, State 1, Line 84
Incorrect syntax near the keyword 'with'. If this statement is a common table
expression, an xmlnamespaces clause or a change tracking context clause, the
previous statement must be terminated with a semicolon.


Am I not using the OUTPUT function correctly and is there an alternative query in SQL?

the ";" after the SELECT terminates the statement. So you have two statements here: SELECT ...; and OUTPUT TO ...; Remove the semicolon after the select and see if that works. docs.microsoft.com/en-us/sql/t-sql/queries/…
– David Buttrick
Jun 29 at 18:21

Thanks, when I remove the semicolon there are still red squiggly lines underlining TO and COLUMN. This is the error: Msg 156, Level 15, State 1, Line 88 Incorrect syntax near the keyword 'TO'. Msg 319, Level 15, State 1, Line 91 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
– nak5120
Jun 29 at 18:29

TO

COLUMN

Msg 156, Level 15, State 1, Line 88 Incorrect syntax near the keyword 'TO'. Msg 319, Level 15, State 1, Line 91 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

2 Answers
2

Why you are trying to do with query. Download some client for SQL , as I know every client provide a feature to export data in various format.

But for complete the answer , you can use SQL Managament Studio for export or object explorer.

I dont want to take credit for this , so you can follow this link for how to do it

https://www.tutorialgateway.org/export-data-from-sql-to-csv/

Thanks, I'm looking to do it with a query so that I can automate the process entirely in SQL without clicking anything.
– nak5120
Jun 29 at 18:00

I actualy use SQLCMD command but there is 7 other ways please check link below:

SQLCMD

EXPORT

OR

Use BCP utility

The -c argument specifies character output, as opposed to SQL's native binary format; this defaults to tab-separated values, but -t , changes the field terminator to commas. -T specifies Windows authentication ("trusted connection"), otherwise use -U MyUserName -P MyPassword.

This doesn't export column headers by default. You need to use a UNION ALL for headers

Thanks, think there is a way I can call that SQL cmd script directly from SSMS? Basically I want to just run a sql query that will do all the data manipulation and export at the end without me clicking anything
– nak5120
Jun 29 at 18:00

I found this hope this is what you want
– Reza Mousazadeh
Jun 29 at 18:15

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.