Export SQL Server query to CSV file

Question

I need to somehow export the result of my SQL query to a CSV file. However, it has to be directly via SQL or VBS.

Or else, all database table data can be exported to CSV.

Answer 1

The utility BCP makes it possible to export data to text file, CSV format. Through the -t option it is possible to define the field separator. I recommend the previous reading of the document Specify field and line terminators.

It is also possible to use the function OPENROWSET to export.

Some articles on the subject:

• How to export database data to text file
• Working with the bcp Command-line Utility
• SQL Server Export to Excel using bcp/sqlcmd Utilities and CSV Files

Answer 2

I was able to export from SQL Server to CSV via the code below:

exec master..xp_cmdshell  'bcp "Select E3TimeStamp, Message, USUARIO, Acked, Severity, FormattedValue, Area, EventType From [c1_alarmes].[dbo].[table_alarmes]" queryout D:\bcp_outputTable.CSV -c -t; -T -S localhost\Testes'

It generated the CVS file after enabling xp_cmdshell via the command:

exec sp_configure 'show advanced options', 1 
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

A question I still have, in the generated file does not show the name of each column. If someone knows how to add this in generation.

Answer 3

With Sqlserver, after performing the query, go to the result and right-click:

The first option of formed to save will be .csv.