I need best practice in T-SQL Export data to CSV (with header)

Question

What I need to do is export data into CSV file using T-SQL.

And I'm very confused about there are many ways can do it, I don't know to choose which one, please help me to confirm the bollowing:

As I know there are about 3 methods, and I want you help me to confirm:

Using Microsoft.Jet.OLEDB.4.0, like this:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                       'Text;Database=C:\Temp\;HDR=Yes;',
                       'SELECT * FROM test.csv')
            (object_id, name)
SELECT object_id, name
  FROM sys.tables;

but this need the csv file is there, and with header

using SQLCMD

command line.

using BCP

Use union, get data and it's column header.

This is all my understanding about T-SQL export to CSV, please help me to confirm.

Is there other way to export to CSV?

Thanks!

Answer 1

Execute the below command in SQL Server:

EXEC xp_cmdshell 'SQLCMD -S . -d MsVehicleReg2 -Q "SELECT * FROM tempViolationInfo" -s "," -o "O:\result.csv"';