We can use BCP (bulk copy program) for exporting SQL table data to a CSV file.
About BCP :
The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into files.
Syntax:
bcp [database_name.] schema.{table_name | view_name | "query" {in data_file | out data_file | queryout data_file | format nul}
[-a packet_size]
[-b batch_size]
[-c]
[-C { ACP | OEM | RAW | code_page } ]
[-d database_name]
[-e err_file]
[-E]
[-f format_file]
[-F first_row]
[-h"hint [,...n]"]
[-i input_file]
[-k]
[-K application_intent]
[-L last_row]
[-m max_errors]
[-n]
[-N]
[-o output_file]
[-P password]
[-q]
[-r row_term]
[-R]
[-S [server_name[\instance_name]]
[-t field_term]
[-T]
[-U login_id]
[-v]
[-V (80 | 90 | 100 )]
[-w]
[-x]
/?
Example:
declare @fileName nvarchar(400)
declare @outputPath nvarchar(400)
declare @query nvarchar(4000)
declare @dbServer varchar(50)
declare @dbName varchar(50)
declare @dbUser varchar(50)
declare @dbPassword varchar(50)
declare @sql nvarchar(4000)
set @fileName ='[FileName]'
set @outputPath = '[OutputPath]' /*Where sql server is installed*/
set @dbServer ='[Server]'
set @dbName = '[DatabaseName]'
set @dbUser = '[DatabaseUser]'
set @dbPassword = '[DatabasePassword]'
set @query ="Select * from [TableName]"
set @sql = N'bcp "' + @query + '" queryout "' + @outputPath + @fileName + '.csv" -S ' + @dbServer + ' -d ' + @dbName + ' -U ' + @dbUser + ' -P ' + @dbPassword + ' -c -t,'
exec xp_cmdshell @sql
