Sql Server Table Export to CSV in Query Result

Sql Server Table Export to CSV in Query Result

Another method to export the sql server table in query result. Useful where user is not authenticate for file system commands.



Declare
@TableName nvarchar(500)
SELECT @TableName = '[SchemaName].[YourTableName]'
DECLARE @FirstColumn bit
DECLARE @ColumnOrder varchar(500)
DECLARE @ColumnName varchar(500)
DECLARE @ColumnType varchar(100)
DECLARE @ColumnsCount int
DECLARE @SelectCommand nVarChar(Max)
DECLARE @ColumnList nVarChar(Max)
DECLARE @TextLine varchar(Max)
DECLARE cColumns CURSOR SCROLL FOR
SELECT Distinct Ordinal_Position As colorder, Column_Name As colName, Data_Type
FROM INFORMATION_SCHEMA.Columns
WHERE '['+ Table_Schema + '].[' + Table_Name + ']' = @TableName
Order by Ordinal_Position


SET @SelectCommand = 'SELECT '
SET @FirstColumn = 1
SET @ColumnList = ''

--Get table's columns & construct SELECT command string
OPEN cColumns
FETCH NEXT FROM cColumns INTO @ColumnOrder, @ColumnName, @ColumnType
WHILE @@FETCH_STATUS = 0
BEGIN

IF @FirstColumn = 0 SET @SelectCommand = @SelectCommand + ' + '' '' + '
SET @FirstColumn = 0
-- binary fields cannot be exported to text file
IF @ColumnType = 'image' OR @ColumnType = 'varbinary' OR @ColumnType = 'binary'
SET @SelectCommand = @SelectCommand + '''NULL'''
ELSE
SET @SelectCommand = @SelectCommand + ' ISNULL(CAST([' + @ColumnName + '] AS varchar(Max)), ''NULL'')'

SET @ColumnList = @ColumnList + '[' + @ColumnName + '],'
FETCH NEXT FROM cColumns INTO @ColumnOrder, @ColumnName, @ColumnType
END
CLOSE cColumns
DEALLOCATE cColumns
SET @SelectCommand = @SelectCommand + ' FROM ' + @TableName --+ ' Order By SourceSystem, Company'
--Select @SelectCommand


-- create
EXEC('DECLARE cTable CURSOR SCROLL FOR ' + @SelectCommand)


SET @TextLine = Reverse(SubString(Reverse(@ColumnList), 2, Len(@ColumnList)))
SET @TextLine = REPLACE(@TextLine, ',', '","')
SET @TextLine = '"' + @TextLine + '"'
-- write line into text file

Print @TextLine



-- get from every row in table @TableName columns and insert into text file (csv)
OPEN cTable
FETCH NEXT FROM cTable INTO @TextLine
WHILE @@FETCH_STATUS = 0
BEGIN
-- remove new line chars
SET @TextLine = REPLACE(@TextLine, CHAR(13), '')
SET @TextLine = REPLACE(@TextLine, CHAR(10), '')
-- put inside string value two " instead of one "
SET @TextLine = REPLACE(@TextLine, '"', '""')
-- replace separator with ,
SET @TextLine = REPLACE(@TextLine, ' ', '","')
SET @TextLine = '"' + @TextLine + '"'
SET @TextLine = REPLACE(@TextLine, 'NULL', '')

Print @TextLine

FETCH NEXT FROM cTable INTO @TextLine
END
CLOSE cTable
DEALLOCATE cTable