Export a data table in CSV format sql server 2008

Question

I need to create a stored procedure which queries a table and creates a CSV file for that data in a specified directory location.

how do i do it?

Answer 1

It is possible to do but you need to have enough rights to run xp_cmdshell and ad hoc distributed queries.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
DECLARE @OutputPath VARCHAR(1000)
SET @OutputPath = 'd:\temp\'
DECLARE @OutputFilename VARCHAR(1000)
DECLARE @ServerName VARCHAR(1000)
SET @ServerName='servername'
DECLARE @Username VARCHAR(50)
SET @Username = 'username'
DECLARE @Password VARCHAR(50)
SET @Password = 'password'
DECLARE @DatabaseName VARCHAR(50)
SET @DatabaseName = 'databasename'
DECLARE @Dsn VARCHAR(1000)
SET @Dsn = 'Server='+@ServerName+';Database='+@DatabaseName+';Uid='+@Username+';Pwd='+@Password+';'

DECLARE @StartProcedureTime DATETIME
DECLARE @EndProcedureTime DATETIME
DECLARE @TimeTaken INTEGER 
DECLARE @QueryToRun VARCHAR(1000)
DECLARE @Query VARCHAR(1000)
DECLARE @result INT

SET @Query = 'mkdir "' + @OutputPath + @DatabaseName +'\"'
EXEC @result = [master]..xp_cmdshell @Query

DECLARE db_cursor CURSOR FOR
SELECT
 T.[FileName] + '.csv' AS [FileName],
 REPLACE(T.SqlToRun, '''', '''''') AS [SqlToRun]
FROM
(
 SELECT 'sp_DataGenerator_AverageRatingAverage' AS [FileName], 'exec sp_DataGenerator_AverageRatingAverage 54' AS [SqlToRun]
 UNION ALL
 SELECT 'sp_DataGenerator_AverageRatingGood', 'exec sp_DataGenerator_AverageRatingGood ''01-01-2009'', ''01-01-2010'''
 UNION ALL
 SELECT 'sp_DataGenerator_AverageRatingBad', 'exec sp_DataGenerator_AverageRatingBad ''01-01-2009'', ''01-01-2010'''
) AS T

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @OutputFilename, @QueryToRun
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @Query = 'bcp "SELECT * FROM OPENROWSET (''SQLOLEDB'','''+@Dsn+''','''+@QueryToRun+''') AS T" queryout '+@OutputPath + @DatabaseName + '\' + @OutputFilename +' -c -t, -r\n -U'+@Username+' -P'+@Password+'  -S'+@ServerName + ' > ' +@OutputPath + @DatabaseName + '\' + @OutputFilename +'.txt'

 SET @StartProcedureTime = getdate() --take start time 
 EXEC @result = [master]..xp_cmdshell @Query
 SET @EndProcedureTime = getdate() --take end time 
 SET @TimeTaken = DATEDIFF(millisecond, @StartProcedureTime, @EndProcedureTime) --take difference in milliseconds.

 PRINT '"'+@QueryToRun + '" took ' + str(@TimeTaken) + ' Milliseconds.'

 FETCH NEXT FROM db_cursor INTO @OutputFilename, @QueryToRun   
END
CLOSE db_cursor  
DEALLOCATE db_cursor

go

sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 0
GO
RECONFIGURE
GO
sp_configure 'Show Advanced Options', 0
GO
RECONFIGURE
GO

Answer 2

You can call BCP via exec xp_cmdshell 'bcp dbname..table out filename.bcp .....'

this is a very similar question except it for importing. Use bcp to import csv file to sql 2005 or 2008

See here for more information on BCP.

Answer 3

You can't do it with a stored procedure directly. I suggest right-clicking your database in SQL Server Management Studio, and selecting "Export Data".

Supply it with a query or a stored procedure that returns rows. Tell it you want the file to be delimited by commas and text-qualified with quotes.

When the wizard asks you if you want to just execute it or save it, save it to disk.

This makes something called an SSIS package. You can then use Windows Explorer to run it, or use a command line program called dtexec.exe to run it with parameters, or give it to your DBA to have them run it inside SQL Server.