Click here to Skip to main content
15,566,064 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick
Posted 5 Oct 2013

Stats

31.2K views
5 bookmarked

Create CSV File in Microsoft SQL

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
27 Jan 2017CPOL
How to create CSV file in 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:

SQL
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.

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:

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
India India
Rajesh is a MCSD, MCAD, MCA and trying to become .Net expert who has more than 9 years experience in Dot net.

Comments and Discussions

 
QuestionAnother alternative with stored procedure Pin
Konstantin Taranov30-Jan-17 22:14
professionalKonstantin Taranov30-Jan-17 22:14 
QuestionAm not able to export the data Pin
Gopi M.G7-Sep-16 21:42
Gopi M.G7-Sep-16 21:42 
Questionvery good tips Pin
$unil Dhiman8-Oct-13 14:43
$unil Dhiman8-Oct-13 14:43 
Hi Rajesh,
this is very good tips to create CSV in SQL.
AnswerRe: very good tips Pin
Rajesh_K_Sharma9-Oct-13 4:18
Rajesh_K_Sharma9-Oct-13 4:18 
QuestionGenerality? Pin
Dewey5-Oct-13 17:44
Dewey5-Oct-13 17:44 
AnswerRe: Generality? Pin
Rajesh_K_Sharma5-Oct-13 19:02
Rajesh_K_Sharma5-Oct-13 19:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.