How to output CSV data with MSSQL

Applies to:

  • Microsoft SQL Server 2005

Data used

USE sql911;
go

SELECT deptno, deptname, mgrno, admrdept FROM department;
go
deptno deptname                             mgrno  admrdept
------ ------------------------------------ ------ --------
A00    SPIFFY COMPUTER SERVICE DIV.         000010 A00
B01    PLANNING                             000020 A00
C01    INFORMATION CENTER                   000030 A00
D01    DEVELOPMENT CENTER                   NULL   A  
D11    MANUFACTURING SYSTEMS                000060 D  
D21    ADMINISTRATION SYSTEMS               000070 D01
E01    SUPPORT SERVICES                     000050 A00
E11    OPERATIONS                           000090 E01
E21    SOFTWARE SUPPORT                     000100 E01

(9 row(s) affected)

Comma Separated Values: CSV

  1. SQL Server Management studio
  2. Navigate to Tools > Options > Query Results > SQL Server > Results to Text > Select: comma delimited
Output to csv
Output to CSV
  • The comma delimited ONLY applies to a new query editor window.
SELECT deptno, deptname, mgrno, admrdept FROM department;
go
deptno,deptname,mgrno,admrdept
A00,SPIFFY COMPUTER SERVICE DIV.,000010,A00
B01,PLANNING,000020,A00
C01,INFORMATION CENTER,000030,A00
D01,DEVELOPMENT CENTER,NULL,A  
D11,MANUFACTURING SYSTEMS,000060,D  
D21,ADMINISTRATION SYSTEMS,000070,D01
E01,SUPPORT SERVICES,000050,A00
E11,OPERATIONS,000090,E01
E21,SOFTWARE SUPPORT,000100,E01

(9 row(s) affected)