Export Sql Data Into Csv File With Header Using Sqlcmd

Here is an example of how to export data from SQL Server table or view to csv file (with header) using sqlcmd utility from T-SQL or command line.

--Method 1
--Export SQL data into csv file with header using sqlcmd
EXEC master..xp_cmdshell 'sqlcmd -E -s"," -W -Q "SELECT * FROM master.DBO.SYSOBJECTS" | findstr /V /C:"-" /B > c:\myfile.csv'
 
--Method 2
-- Export SQL data into csv file with header using sqlcmd and a view to avoid - replacement
CREATE VIEW vw_MyView
AS
SELECT 'NAME' AS NAME, 'ID' AS ID, 'XTYPE' AS XTYPE 
UNION ALL
SELECT NAME, CONVERT(VARCHAR,ID), XTYPE FROM master.DBO.SYSOBJECTS
 
EXEC master..xp_cmdshell 'SQLCMD -E -Q "SET NOCOUNT ON; SELECT NAME, ID, XTYPE FROM dbo.vw_MyView" -o "c:\myfile.csv" -h-1 -s "," -W'
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License