sqlserver 脚本方式导出数据到excel

  1 use EntDataCenter
  2 go
  3 SET ANSI_NULLS ON
  4 GO
  5 SET QUOTED_IDENTIFIER ON
  6 GO
  7 -- =============================================
  8 -- Author:        <Author,,Name>
  9 -- Create date: <Create Date,,>
 10 -- Description:    <Description,,>
 11 -- =============================================
 12 --exec dbo.ent_all_DataToExcel
 13 create PROCEDURE  ent_all_DataToExcel
 14 
 15 AS
 16 BEGIN
 17     -- SET NOCOUNT ON added to prevent extra result sets from
 18     -- interfering with SELECT statements.
 19     SET NOCOUNT off;
 20     DECLARE @str2name varchar(8000);--二级国代分类名称
 21     DECLARE @str2code varchar(8000);--二级国代分类代码
 22     DECLARE @str1name varchar(8000);--一级国代分类名称
 23     DECLARE @str varchar(8000);
 24     DECLARE @str1 varchar(8000);
 25     DECLARE @server varchar(100);--服务器
 26     DECLARE @uname varchar(100);--用户名
 27     DECLARE @pwd varchar(100);--密码
 28     
 29     DECLARE @IsExist bit;
 30     select @str2name=‘‘;
 31     select @str2code=‘‘;
 32     select @str1name=‘‘;
 33     select @str=‘‘;
 34     select @str1=‘‘;
 35     set @server=192.168.1.7;
 36     set @uname=dev_db;
 37     set @pwd=dev_db;
 38     set @IsExist=0;
 39     
 40     ---判断文件夹是否存在,若不存在则创建文件夹----
 41     begin
 42         CREATE TABLE #tmp ([File Exists] BIT, [File is a Directory] BIT, [Parent Directory Exists] BIT)
 43         INSERT INTO #tmp ([File Exists], [File is a Directory], [Parent Directory Exists])
 44         EXEC master.dbo.xp_fileexist c:\project;
 45         SELECT @IsExist=[File is a Directory] FROM #tmp;
 46         if(@IsExist=0)
 47         begin
 48           ExEc xp_cmdshell mkdir c:\project;
 49         end
 50         drop table #tmp
 51     end
 52     
 53     DECLARE contact_cursor CURSOR FOR (SELECT   a.国代分类名称 as 二级国代分类名称,a.国代分类代码 as 二级国代分类代码,b.国代分类名称 as 一级国代分类名称
 54                                         FROM [EntDataCenter].[dbo].[doc_class_nation_code]  a
 55                                         inner join   [EntDataCenter].[dbo].[doc_class_nation_code]   b
 56                                         on a.PCode=b.国代分类代码  where a.Level=1)
 57     OPEN contact_cursor
 58             FETCH NEXT FROM contact_cursor
 59             INTO @str2name,@str2code,@str1name
 60             WHILE @@FETCH_STATUS = 0
 61             BEGIN        
 62                          begin
 63                         set @str=select   a.[企业名称],a.[所在省份],a.[所在城市], ;
 64                         set @str=@str+(case b.year when 2005  then b.[total_assets] else 0 end) as [2005资产],;
 65                         set @str=@str+(case b.year when 2006  then b.[total_assets] else 0 end) as [2006资产],;
 66                         set @str=@str+(case b.year when 2007  then b.[total_assets] else 0 end) as [2007资产],;
 67                         set @str=@str+(case b.year when 2008  then b.[total_assets] else 0 end) as [2008资产],;
 68                         set @str=@str+(case b.year when 2009  then b.[total_assets] else 0 end) as [2009资产],;
 69                         set @str=@str+(case b.year when 2010  then b.[total_assets] else 0 end) as [2010资产],;
 70                         set @str=@str+(case b.year when 2011  then b.[total_assets] else 0 end) as [2011资产],;
 71                         set @str=@str+(case b.year when 2012  then b.[total_assets] else 0 end) as [2012资产],;
 72                         set @str=@str+(case b.year when 2005  then b.total_current_assets else 0 end) as [2005流动资产],;
 73                         set @str=@str+(case b.year when 2006  then b.total_current_assets else 0 end) as [2006流动资产],;
 74                         set @str=@str+(case b.year when 2007  then b.total_current_assets else 0 end) as [2007流动资产],;
 75                         set @str=@str+(case b.year when 2008  then b.total_current_assets else 0 end) as [2008流动资产],;
 76                         set @str=@str+(case b.year when 2009  then b.total_current_assets else 0 end) as [2009流动资产],;
 77                         set @str=@str+(case b.year when 2010  then b.total_current_assets else 0 end) as [2010流动资产],;
 78                         set @str=@str+(case b.year when 2011  then b.total_current_assets else 0 end) as [2011流动资产],;
 79                         set @str=@str+(case b.year when 2012  then b.total_current_assets else 0 end) as [2012流动资产],;
 80                         set @str=@str+(case b.year when 2005  then b.total_liabilities else 0 end) as [2005负债],;
 81                         set @str=@str+(case b.year when 2006  then b.total_liabilities else 0 end) as [2006负债],;
 82                         set @str=@str+(case b.year when 2007  then b.total_liabilities else 0 end) as [2007负债],;
 83                         set @str=@str+(case b.year when 2008  then b.total_liabilities else 0 end) as [2008负债],;
 84                         set @str=@str+(case b.year when 2009  then b.total_liabilities else 0 end) as [2009负债],;
 85                         set @str=@str+(case b.year when 2010  then b.total_liabilities else 0 end) as [2010负债],;
 86                         set @str=@str+(case b.year when 2011  then b.total_liabilities else 0 end) as [2011负债],;
 87                         set @str=@str+(case b.year when 2012  then b.total_liabilities else 0 end) as [2012负债],;
 88                         set @str=@str+(case b.year when 2005  then b.operating_income else 0 end) as [2005收入],;
 89                         set @str=@str+(case b.year when 2006  then b.operating_income else 0 end) as [2006收入],;
 90                         set @str=@str+(case b.year when 2007  then b.operating_income else 0 end) as [2007收入],;
 91                         set @str=@str+(case b.year when 2008  then b.operating_income else 0 end) as [2008收入],;
 92                         set @str=@str+(case b.year when 2009  then b.operating_income else 0 end) as [2009收入],;
 93                         set @str=@str+(case b.year when 2010  then b.operating_income else 0 end) as [2010收入],;
 94                         set @str=@str+(case b.year when 2011  then b.operating_income else 0 end) as [2011收入],;
 95                         set @str=@str+(case b.year when 2012  then b.operating_income else 0 end) as [2012收入],;
 96                         set @str=@str+(case b.year when 2005  then b.operating_costs else 0 end) as [2005成本],;
 97                         set @str=@str+(case b.year when 2006  then b.operating_costs else 0 end) as [2006成本],;
 98                         set @str=@str+(case b.year when 2007  then b.operating_costs else 0 end) as [2007成本],;
 99                         set @str=@str+(case b.year when 2008  then b.operating_costs else 0 end) as [2008成本],;
100                         set @str=@str+(case b.year when 2009  then b.operating_costs else 0 end) as [2009成本],;
101                         set @str=@str+(case b.year when 2010  then b.operating_costs else 0 end) as [2010成本],;
102                         set @str=@str+(case b.year when 2011  then b.operating_costs else 0 end) as [2011成本],;
103                         set @str=@str+(case b.year when 2012  then b.operating_costs else 0 end) as [2012成本],;
104                         set @str=@str+(case b.year when 2005  then b.total_profit else 0 end) as [2005利润总额],;
105                         set @str=@str+(case b.year when 2006  then b.total_profit else 0 end) as [2006利润总额],;
106                         set @str=@str+(case b.year when 2007  then b.total_profit else 0 end) as [2007利润总额],;
107                         set @str=@str+(case b.year when 2008  then b.total_profit else 0 end) as [2008利润总额],;
108                         set @str=@str+(case b.year when 2009  then b.total_profit else 0 end) as [2009利润总额],;
109                         set @str=@str+(case b.year when 2010  then b.total_profit else 0 end) as [2010利润总额],;
110                         set @str=@str+(case b.year when 2011  then b.total_profit else 0 end) as [2011利润总额],;
111                         set @str=@str+(case b.year when 2012  then b.total_profit else 0 end) as [2012利润总额];
112                         set @str=@str+from [dbo].[ent_all] a ;
113                         set @str=@str+left join [dbo].[Ent_All_Finance] b on a.ent_id = b.ent_id  where a.国代分类代码 like ‘‘‘+@str2code+%‘‘‘;
114                         
115                         --set  @str=‘select * from [EntDataCenter].[dbo].[doc_class_nation_code] where Level=1‘;
116                         set @str1=bcp "+@str+" queryout C:\project\+@str1name+-+@str2name+.xls -c -S+@server+ -U+@uname+ -P+@pwd+‘‘;
117                         end 
118                         --print @str1;
119                         EXEC master..xp_cmdshell @str1
120                        
121                        
122               FETCH NEXT FROM contact_cursor
123               INTO @str2name,@str2code,@str1name
124             END
125             CLOSE contact_cursor
126     DEALLOCATE contact_cursor
127  
128 END
129 GO

 

sqlserver 脚本方式导出数据到excel

上一篇:数据库设计——11 个重要的数据库设计规则


下一篇:sql生成excel