Export SQL table data to a CSV file

 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