Adding to the previous answer, which helps you automate the action, if you need it only from time to time you can do it in Management Studio, simply right click on the header - Save results as -> choose a .csv file.

Or if you want to do that for each select statement that you run, you can change the output direction of the results to a file. Use Tools -> Options -> Query Results - Results to file.

Another way, that can be automated easily, and makes use of SSIS, is by using Management Studio's Export Data feature. Right click on the database -> Tasks -> Export data. There is a wizard with lots of options there. You should choose the source database, the destination and other options. For the destination make sure it's "Flat file", browse and choose .csv type, choose whatever formatting you need and at the end you'll obtain an SSIS package that can be saved locally and repeated on need.

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's 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.

Low tech, but...

select
   col1 + ','
+  cast(col2 as varchar(10)) + ','
+  col3
from mytable;

It looks like you already have an acceptable solution, but if you have db mail set up you could potentially do something like this:

EXEC msdb.dbo.sp_send_dbmail
    @recipients='[email protected]',
    @subject='CSV Extract', 
    @body='See attachment',
    @query ='SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'CSV_Extract.txt',
    @query_result_separator = ',',
    @query_result_header = 1

If you are emailing the file somewhere anyway, it might save you a few steps.

This can easily be done in a 3 step process:

1. Write your query as a SELECT INTO query. This will essentially export your query results into a table.

   Select
     Field1
    ,Field2
    ,Field3
   INTO dbo.LogTableName         --Table where the query Results get logged into.
   From Table
   

   Note, this table cannot exist when the query runs, as this type of query wants to create the table as part of the execution.

2. Then use SSIS to export the results of that table to .csv. The SSIS Export wizard lets you pick your delimiter, etc. This is really handy for using a | delimiter in instances where the result set contains commas.

   Right click on DB Name > Tasks > Export Data

3. Once the Export is complete, execute the DROP TABLE command to cleanup your log table.

   Drop Table dbo.LogTableName
   

This procedure will generate a CSV file with the output of the query you pass as a parameter. The 2nd parameter is your CSV file name, make sure you pass the file name with a valid writable path.

Here is the script to create the procedure:

CREATE procedure [dbo].[usp_create_csv_file_from_query]
    @sql Nvarchar(MAX),
    @csvfile Nvarchar(200)
    as
    BEGIN
        if IsNull(@sql,'') = '' or IsNull(@csvfile,'') = ''
        begin
            RAISERROR ('Invalid SQL/CsvFile values passed!; Aborting...', 0, 1) WITH NOWAIT
            return
        end
        DROP TABLE if exists global_temp_CSVtable;
        declare 
            @columnList varchar(4000), 
            @columnList1 varchar(4000),
            @sqlcmd varchar(4000),
            @dos_cmd nvarchar(4000)

        set @sqlcmd = replace(@sql, 'from', 'into global_temp_CSVtable from')
        EXECUTE (@sqlcmd); 

        declare @cols table (i int identity, colname varchar(100))
        insert into @cols
        select column_name
        from information_schema.COLUMNS
        where TABLE_NAME = 'global_temp_CSVtable'

        declare @i int, @maxi int
        select @i = 1, @maxi = MAX(i) from @cols

        while(@i <= @maxi)
        begin
            select @sql = 'alter table global_temp_CSVtable alter column [' + colname + '] VARCHAR(max) NULL'
            from @cols
            where i = @i
            exec sp_executesql @sql
            select @i = @i + 1
        end

        SELECT 
             @columnList = COALESCE(@columnList + ''', ''', '') + column_name 
            ,@columnList1 = COALESCE(@columnList1 + ', ', '') + column_name
        from information_schema.columns
        where table_name =  'global_temp_CSVtable'
        ORDER BY ORDINAL_POSITION;

        SELECT @columnList = '''' + @columnList + '''';

        SELECT @dos_cmd='BCP "SELECT ' + @columnList + 
                        ' UNION ALL ' +
                        'SELECT * from ' + db_name() + '..global_temp_CSVtable" ' + 
                        'QUERYOUT ' + @csvfile + 
                        ' -c -t, -T'
        exec master.dbo.xp_cmdshell @dos_cmd, No_output 
        DROP TABLE if exists global_temp_CSVtable;
    end

I use MS Access DoCMD to export CSV's from SQL Server. It is able to do it with a single command. The access database is only used to enable the access command. It performs well, and as an added benefit (not sure why) it creates a schema.ini for the table exported.

Adam

CMD="SELECT * INTO [Text;HDR=Yes;DATABASE=C:\].[Results.txt]  
FROM [ODBC;DSN=SQL2017;Description=SQL2017;UID=.;Trusted_Connection=Yes;APP=Microsoft Office 2013;WSID=LAPTOP-XYZ;DATABASE=TempDB_DataDump_1].Results "

 Set appAccess = CreateObject("Access.Application") 
                appAccess.OpenCurrentDatabase "anyAccessdatabase.mdb" 
                   ' appAccess.DoCmd.SetWarnings 0
                    appAccess.DoCmd.runsql "CMD"