BCP导出文件

CREATE      PROCEDURE [dbo].[p_To_Csv]
@path varchar(50)
as
begin

	/**
	* 将统计好的数据存放到指定路径的csv文件中 
	* csv文件的命名规则如下 其中 20100118为日期~~ 
	* 作业一般为凌晨2点10分执行 ，生成的是前一天的数据
	* @path 传入的路径
	*  
	*/
	if(@path='')
	set @path='D:\backup'

   	declare @folderexist int 
    	declare @intime datetime 
	declare @txtname varchar(50),@datestr varchar(8) , @fullpath varchar(200),@sql varchar (1000) 
	declare @year varchar(4),@month varchar(2),@day varchar(2)
	declare @bcpstr varchar(1100) ,@bcpstrfolder varchar(1000),@bcpstrcreate varchar(1000)
	  

--------------------------------------------------------
	set @sql =' select '''+'timeLine'+''','''+'RadiusNum'+''' '
	+' union all '
	+ 'select timeline,cast(radiusNum as varchar(20)) from wapLog..radiusStat'
	  
    set @intime =dateadd(day,-1,getdate())
	set @year=cast(year(@intime) as varchar(4))
	set @month=cast(month(@intime) as varchar(2))
	set @day=cast(day(@intime) as varchar(2))
	
	if (substring(@month,1,1)<>'0' and len(@month)=1) set @month='0'+@month
	if (substring(@day,1,1)<>'0' and len(@day)=1) set @day='0'+@day
	
	--如果文件夹不存在 主动创建
	set @bcpstrfolder='cd /d '+@path
	exec @folderexist=master..xp_cmdshell @bcpstrfolder,no_output
	if @folderexist<>0
	begin
		set @bcpstrcreate='md '+@path
		exec   master..xp_cmdshell @bcpstrcreate
	end
    ---拼接 路径 文件名 
	set @datestr=@year+@month+@day
	set @txtname=aaaa_'+@datestr+'.csv'
	set @fullpath=@path+@txtname
	if(right(@path,1)<>'\') set @fullpath=@path+'\'+@txtname

	--print(@fullpath)
    ------生成到Csv文件
	set @bcpstr='bcp "'+@sql+' " queryout '+@fullpath+' -t, -c -T'
		 print @bcpstr	
		exec master..xp_cmdshell @bcpstr
	end

	
GO