动态导出数据到文本/xls/sql/doc文件

select IDENTITY(int, 1,1) AS [id],'insert into tt(filetype,Brand,ProdNo,ProdType ,filetitle,C0001,Tmoney,HttpUrl,parentid) values(' +''''+'G'+''''+',' +''''+isnull(a.EC0102,'')+''''+',' +''''+isnull(a.EC0107,'')+''''+',' +''''+isnull(b.EC0123,'')+''''+',' +''''+isnull(EC0104,'') +case when isnull(EC0103,'')<>'' then '('+isnull(EC0103,'')+')' else '' end+''''+',' +''''+isnull(a.EC0118,'')+''''+',' +''''+cast(isnull(b.EM0125,0) as varchar)+''''+',' +''''+isnull(a.EC0982,'')+''''+',' +''''+c.a+'''' +');' memo into temptb_01 from t1 a join t2 b on a.EC0102=b.EC0102 and a.EC0107=b.EC0107 left join tb_1 c on a.objjc=c.b where a.objid<>1 and a.EC0102 in('Abnova','Accurate' ,'Anatrace') --动态导出数据到文本文件/excel/word中 declare @sql varchar(8000) --查询的语句 ,@filename varchar(50)--文件名称 ,@filetype varchar(20)--文件类型 ,@i int --次数 ,@str varchar(8000)--输出结果 ,@servername varchar(200)--服务器名称 ,@username varchar(200)--登录用户名 ,@pwd varchar(200)--登录密码 ,@temp varchar(100) ,@pagesize int--每页导出记录条数 set @i=32 set @pagesize=5000 set @filename='data' set @filetype='.sql' set @servername='TANGJIGANG/CENTAUR_TOM' set @username ='sa' set @pwd='sa' while @i>=0 begin set @sql='"Select memo from erp_prod..temptb_01 where id<='+cast(@pagesize*(@i+1) as varchar)+' and id>'+cast(@pagesize*@i as varchar)+'"' select @str='master..xp_cmdshell '' bcp '+@sql +' queryout c:/test/'+@temp+@filetype+' -c -S'+@servername+' -U'+@username+' -P'+@pwd+'''' select @i=@i-1 select @temp=@filename+cast(@i as varchar) select @str='exec( '+''''+replace(@str,'''','''''')+''')' exec(@str)