Exporting Database/Table to CSV in a Stored Procedure...quickly December, 2006
We had a situation come up where we needed to quickly export about 3000 tables from SQL Server to CSV. We bought a tool in hopes that we could do this quickly (Why wont DTS export to multiple text files?!?), well the tool stunk and it would have literally taken a week to complete. So I wrote a two SP's that use BCP to export an entire database or a single table to CSV (Or use whatever delimiter and qualifier you want). I whiped them up pretty quick so they could probably use some fine tuning if you are going to use them in production. Two things to note:
1) In SQL Server 2005 you have to explicitly turn on the ability to use xp_cmdshell in order to make the BCP call.
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
2) Since the table selection is built on the fly (To add delimiters, qualifiers, column names and escape chars in data that match the qualifier) tables with a lot of columns will fail. The selection statement is peiced together in a VARCHAR(8000) so there is limited space.
3) I'm creating a temporary view in which to pull the exported data from because the call to xp_cmdshell is limited to 1k in SQL Server 2000 (I tried it without the view in 2005 and it doesent appear to have this limitation).
Here is the proc that exports the entire DB:
CREATE PROCEDURE [dbo].[ExportDatabase]
@Database varchar(256),
@OutputPath varchar(1000),
@OutputExtension varchar(50) = 'csv',
@Delimiter varchar(50) = ',',
@Qualifier varchar(50) = '"',
@IncludeColumnHeaders bit = 1
AS
BEGIN
DECLARE @TableName varchar(256)
DECLARE @ColumnCursorSQL varchar(500)
SET @ColumnCursorSQL = 'DECLARE TableCursor CURSOR FAST_FORWARD FOR SELECT name FROM ' + @Database + '..' + 'sysobjects WHERE type=''U'''
EXEC (@ColumnCursorSQL)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC ExportTable
@Database,
@TableName,
@OutputPath,
@OutputExtension,
@Delimiter,
@Qualifier,
@IncludeColumnHeaders
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
END
Here is the proc that exports a single table:
CREATE PROCEDURE [dbo].[ExportTable]
@Database varchar(256),
@Table varchar(256),
@OutputPath varchar(1000),
@OutputExtension varchar(50) = 'csv',
@Delimiter varchar(50) = ',',
@Qualifier varchar(50) = '"',
@IncludeColumnHeaders bit = 1
AS
BEGIN
DECLARE @ColumnNameSQL varchar(8000)
DECLARE @SelectSQL varchar(8000)
DECLARE @ColumnName varchar(256)
DECLARE @FirstColumn bit
SET @FirstColumn = 1
SET @SelectSQL = 'SELECT '
SET @ColumnNameSQL = 'SELECT '
DECLARE @ColumnCursorSQL varchar(500)
SET @ColumnCursorSQL = 'DECLARE ColumnCursor CURSOR FAST_FORWARD FOR SELECT name FROM ' + @Database + '..' + 'syscolumns WHERE id=object_id(''' + @Database + '..' + @Table + ''')'
EXEC (@ColumnCursorSQL)
OPEN ColumnCursor
FETCH NEXT FROM ColumnCursor INTO @ColumnName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF @FirstColumn = 1
SET @FirstColumn = 0
ELSE
BEGIN
SET @SelectSQL = @SelectSQL + @Delimiter
IF @IncludeColumnHeaders = 1
SET @ColumnNameSQL = @ColumnNameSQL + @Delimiter
END
IF @IncludeColumnHeaders = 1
SET @ColumnNameSQL = @ColumnNameSQL + '''' +
@Qualifier + @ColumnName + @Qualifier + ''' AS ' + @ColumnName
SET @SelectSQL = @SelectSQL + '''' + @Qualifier +
''' + REPLACE(CONVERT(varchar(8000), ' + @ColumnName +
'), ''' + @Qualifier + ''', ''' + @Qualifier + @Qualifier + ''') + ''' + @Qualifier + ''' AS ' + @ColumnName
FETCH NEXT FROM ColumnCursor INTO @ColumnName
END
CLOSE ColumnCursor
DEALLOCATE ColumnCursor
SET @SelectSQL = @SelectSQL + ' FROM ' + @Database + '..' + @Table
IF @IncludeColumnHeaders = 1
SET @SelectSQL = @ColumnNameSQL + ' UNION ALL ' + @SelectSQL
DECLARE @ExportTempViewName varchar(50)
SET @ExportTempViewName = + 'ExportTemp' + REPLACE(CONVERT(varchar(36), NEWID()), '-', '')
DECLARE @ExportTempViewCreate varchar(8000)
SET @ExportTempViewCreate = 'CREATE VIEW ' + @ExportTempViewName + ' AS ' + @SelectSQL
EXEC (@ExportTempViewCreate)
SET @SQL = 'bcp "SELECT * FROM ' + db_name() + '..' + @ExportTempViewName + '" queryout "' +
@OutputPath + '\' + @Database + '.' + @Table + '.' +
@OutputExtension + '" -c -t, -T -S' + @@servername
EXEC master..xp_cmdshell @SQL
DECLARE @ExportTempViewDrop varchar(8000)
SET @ExportTempViewDrop = 'DROP VIEW ' + @ExportTempViewName
EXEC (@ExportTempViewDrop)
END