MSSQL ייצוא טבלה/VIEW לקובץ CSV

[softs]

CREATE PROC [dbo].[ExportTable] (@TableName NVARCHAR(MAX) , @FileName NVARCHAR(MAX)) AS

DECLARE @FileContents VARCHAR(MAX)
DECLARE @FieldNames VARCHAR(MAX)

SET  @FieldNames = 
(SELECT SUBSTRING(( SELECT ',' + '' + name + '' FROM sys.columns WHERE Object_id = Object_ID(@TableName)FOR XML PATH('')),2,200000) AS CSV)

SET @FileContents = 
(SELECT SUBSTRING(( SELECT ',' + 'CHAR(34) + CAST([' + name + '] AS NVARCHAR(MAX)) + CHAR(34)' FROM sys.columns WHERE Object_id = Object_ID(@TableName)FOR XML PATH('')),2,200000) AS CSV)

declare @sql varchar(8000)
select @sql = 'bcp "select ''' + @FieldNames + '''" queryout ' + @FileName + '_headers.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

select @sql = 'bcp "select ' + @FileContents + ' from ' + DB_NAME() + '..' + @TableName + '" queryout ' + @FileName + '_Lines.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

DECLARE @FinalFileName AS NVARCHAR(MAX) = '' + @FileName + '.csv'

SET @sql = 'copy ' + @FileName + '_headers.csv+' + @FileName + '_Lines.csv ' + @FinalFileName

exec master..xp_cmdshell @sql

SET @sql = 'del ' + @FileName + '_headers.csv, ' + @FileName + '_Lines.csv'

exec master..xp_cmdshell @sql

שימוש לדוגמה

[ExportTable] 'Monthly_DailyFlatFile', 'C:\test1'

פורסם במקור בפורום CODE613 ב08/02/2015 15:22 (+02:00)