Create CSV File in Microsoft SQL

Introduction

This tip shows how to create a CSV file in Microsoft SQL.

Background

Generating CSV file from SQL is very simple. I have created Test3 database and one table Person. Now, my objective is to generate CSV file of Person record.

Please execute the below scripts for creating database and table:

CREATE DATABASETEST3
USE [TEST3]
GO
/******Object:  Table [dbo].[Person]    Script Date: 10/05/2013 10:00:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [FirstName] [nvarchar](50) NULL,
      [LastName] [nvarchar](50) NULL,
 CONSTRAINT[PK_Person] PRIMARY KEYCLUSTERED 
(
      [Id] ASC
)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE  = OFF, _
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Person]ON
INSERT [dbo].[Person] ([Id], [FirstName], [LastName]) VALUES (1, N'Rajesh', N'Sharma')
INSERT [dbo].[Person] ([Id], [FirstName], [LastName]) VALUES (2, N'Kumar', N'Sharma')
SET IDENTITY_INSERT [dbo].[Person] OFF

In the below scripts, what you have to do is set the variable as per your configuration like @Path is the physical location of file where file will be created, @DBName is database name. In the given below example, I have used Test3. @ServerName as the instance of SQL.

DECLARE @path VARCHAR(1000) 
DECLARE @DBName varchar(255)
DECLARE@ServerName varchar(255)
--- DBDETAILS-----------------------
SET@ServerName ='ServerName'
SET @DBName ='TEST3'
SET @path='E:\Test' -- File location where you want to generate csv file
EXEC sp_configure 'xp_cmdshell', 1
      RECONFIGURE
      declare @fileName NVARCHAR(30)
      SET @fileName = 'TEST_' + CONVERT(nvarchar(30),_
      GETDATE(),112) --- FILE NAME WITH DATE
      declare @cmd varchar(500)

      set @cmd='bcp "DECLARE @colnamesVARCHAR(max);_
      Select @colnames = COALESCE(@colnames + '','', '''') + _
      column_namefrom ' + @DBName +'.INFORMATION_SCHEMA.COLUMNS _
      whereTABLE_NAME=''PERSON'';select @colnames; " queryout _
      '+@path+'\Header.csv  -T -c -S'+@ServerName
      print '1'+@cmd
      exec master.dbo.xp_cmdShell @cmd

      set @cmd='bcp "select * from  ' +@DBName + _
      '.dbo.PERSON"queryout '+@path+'\Detail.csv -t, -T -c -S '+@ServerName
      exec master.dbo.xp_cmdShell @cmd

      set @cmd='copy /b '+@path+'\Header.csv+'+_
      @path+'\Detail.csv '+@path+'\' + @fileName + '.csv'
      print '2'+ @cmd
      exec master.dbo.xp_cmdShell @cmd

      set @cmd='del '+@path+'\Header.csv '+@path+'\Detail.csv'
      exec master.dbo.xp_cmdShell @cmd

Note: xp_cmdshell' does not exist, then please execute the below script:

EXEC sp_configure 'show advanced options', _
1;GORECONFIGURE;GOEXEC sp_configure'xp_cmdshell',1GORECONFIGUREGO