copy data to text file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

copy data to text file

Author

Topic

purisqlserver
Yak Posting Veteran

73 Posts

Posted - 2001-06-13 : 09:23:24

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

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)
)
as
if LTRIM(RTRIM(@sql)) = '' or LTRIM(RTRIM(@fullFileName)) = '' or LTRIM(RTRIM(@UniqueTempTblName))=''
begin
return -1
end
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 master
select @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 table
declare @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 > 0
begin
select -1 as ReturnValue -- failure
return
end
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT @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_name
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = @TempTable1

-- execute select query to insert data and column names into new temp table
IF @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]'
END
ELSE
BEGIN
SET @sql = 'select ' + @columnNames + ' into '+ @TempTable2 + ' from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from '+@TempTable1+ ') t order by [temp##SortID]'
END
exec (@sql)

-- build full BCP query
set @sql = 'bcp "select * from '+@TempTable2+'" queryout '+ @fullFileName +' -c -T'
-- execute BCP( turn on xp_cmdshell first)

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE

Exec master..xp_cmdshell @sql

EXECUTE sp_configure 'xp_cmdshell', '0'
RECONFIGURE WITH OVERRIDE
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

--turn off xp_cmdshell
if @@error > 0
begin
return -1 -- failure
end
SET @TempSQL ='drop table ' + @TempTable1
EXEC (@TempSQL)
SET @TempSQL ='drop table ' + @TempTable2
EXEC (@TempSQL)
return 0

RUN :

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'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources