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
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)
SET@ServerName ='ServerName'
SET @DBName ='TEST3'
SET @path='E:\Test'
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
declare @fileName NVARCHAR(30)
SET @fileName = 'TEST_' + CONVERT(nvarchar(30),_
GETDATE(),112)
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
Rajesh is a MCSD, MCAD, MCA and trying to become .Net expert who has more than 9 years experience in Dot net.