Technical Article

Scrip to export excel file in a Loop

,

Script to Get Exl Output in a Do while loop. Example taken is Authors table in PUB database, where excel is generated for each state the file in state name.

U can modify the script to include filename in u'r format (eg. state + date)

create  procedure usp_runbcppkg
as
declare @as_bcp varchar(255)
declare @as_query nvarchar(255)
declare @as_select varchar(255)
declare @as_appcode varchar(20)

set @as_select = 'bcp "select appcode, servername, region, usage_ctr, st_date, ed_date from pubs..temp"' 
set @as_query = 'master.dbo.xp_cmdshell ' + @as_bcp

begin
   --cursor to get the list of state
   declare cur_appcode cursor for
   select distinct state from authors
order by 1

   open cur_appcode
   fetch next from cur_appcode into @as_appcode
   while @@fetch_status = 0
   begin
---Truncate & Insert the date for the state in Temp Table
truncate table temp

insert into temp (au_id, au_lname, phone, address, city, state, zip)
select au_id, au_lname, phone, address, city, state, zip
from pubs..authors where state = @as_appcode
--<Servername> to be entered
--<Password> to be entered
   set @as_bcp = @as_select +  ' queryout c:\temp\' +  @as_appcode + '.xls -S<Servername> -Usa -P<Password> -w'
exec master..xp_cmdshell @as_bcp
fetch next from cur_appcode into @as_appcode
   end
   close cur_appcode
   deallocate cur_appcode
end

Rate

Share

Share

Rate