| Author |
 Topic  |
|
|
purisqlserver
Yak Posting Veteran
India
73 Posts |
 Posted - 06/13/2001 : 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
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
|
|
alexkreyn
Starting Member
USA
9 Posts |
Posted - 06/27/2007 : 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'
|
 |
|
| |
Topic |
|
|
|
copy data to text file
