alexkreyn
Starting Member
9 Posts |
Posted - 2007-06-27 : 10:36:23
|
quote: Originally posted by purisqlserver sir,How do we copy selected data from a table to text file using stored procedures.It should be using SP,since it has to be placed as job in sql server.:)thank you
CREATE proc [dbo].[spExportData] ( @dbName varchar(100) = 'master', @sql varchar(5000) = '', @fullFileName varchar(100) = '', @ShowHeader bit =1, @UniqueTempTblName varchar(100))asif LTRIM(RTRIM(@sql)) = '' or LTRIM(RTRIM(@fullFileName)) = '' or LTRIM(RTRIM(@UniqueTempTblName))=''begin return -1end SET @UniqueTempTblName=LTRIM(RTRIM(@UniqueTempTblName))DECLARE @TempTable1 varchar(100)DECLARE @TempTable2 varchar(100)DECLARE @TempSQL varchar(8000)SET @TempTable1 = '##'+ @UniqueTempTblName +'_1'SET @TempTable2 = '##'+ @UniqueTempTblName +'_2'-- if DB isn't passed in set it to masterselect @dbName = 'use ' + @dbName + ';'if object_id(@TempTable1) is not null SET @TempSQL='drop table '+ @TempTable1 EXEC (@TempSQL)if object_id(@TempTable2) is not null SET @TempSQL='drop table '+ @TempTable2 EXEC (@TempSQL)-- insert data into a global temp tabledeclare @columnNames varchar(8000), @columnConvert varchar(8000)select @tempSQL = left(@sql, charindex('from', @sql)-1) +' into ' + @TempTable1 + ' ' + substring(@sql, charindex('from', @sql)-1, len(@sql))exec(@dbName + @tempSQL)if @@error > 0begin select -1 as ReturnValue -- failure returnend -- build 2 lists-- 1. column names-- 2. columns converted to nvarcharSELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name, @columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),' + column_name + case when data_type in ('datetime', 'smalldatetime') then ',121' when data_type in ('numeric', 'decimal') then ',128' when data_type in ('float', 'real', 'money', 'smallmoney') then ',2' when data_type in ('datetime', 'smalldatetime') then ',120' else '' end + ') as ' + column_nameFROM tempdb.INFORMATION_SCHEMA.ColumnsWHERE table_name = @TempTable1-- execute select query to insert data and column names into new temp tableIF @ShowHeader = 1 BEGIN SET @sql = 'select ' + @columnNames + ' into '+ @TempTable2 + ' from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from '+@TempTable1+ ' union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]' ENDELSE BEGIN SET @sql = 'select ' + @columnNames + ' into '+ @TempTable2 + ' from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from '+@TempTable1+ ') t order by [temp##SortID]' ENDexec (@sql)-- build full BCP queryset @sql = 'bcp "select * from '+@TempTable2+'" queryout '+ @fullFileName +' -c -T' -- execute BCP( turn on xp_cmdshell first)EXECUTE sp_configure 'show advanced options', 1RECONFIGURE WITH OVERRIDEEXECUTE sp_configure 'xp_cmdshell', '1'RECONFIGURE WITH OVERRIDEExec master..xp_cmdshell @sqlEXECUTE sp_configure 'xp_cmdshell', '0'RECONFIGURE WITH OVERRIDEEXECUTE sp_configure 'show advanced options', 0RECONFIGURE WITH OVERRIDE--turn off xp_cmdshellif @@error > 0 begin return -1 -- failure endSET @TempSQL ='drop table ' + @TempTable1EXEC (@TempSQL)SET @TempSQL ='drop table ' + @TempTable2EXEC (@TempSQL)return 0RUN :declare @sql varchar(6800), @dbName varchar(100), @fullFileName varchar(100)select @dbName = 'ian_www', @sql = 'select top 10000 * from ian_www.dbo.v_form_data', @fullFileName = 'c:\yys1.txt'exec master..spExportData @dbName, @sql, @fullFileName ,1,'x' |
|
|