SQLServer 定时导出数据CSV格式

应该不少的人碰到过经常性的隔段时间就要到数据库里,导出某些数据,给某些人,这过程涉及了连接服务器,查询,右键另存为,压缩,传到本地,然后通过QQ或者其他方式传给对方。过程并不难,但是很烦啊!!!

故特意将上述动作转化为自动化处理,可分为两步,首先sqlserver定时导出文件到某个目录;其次,利用bat脚本将该目录下的相应文件压缩,并且复制到一个台web服务器发布目录下(这个也可以是ftp,具体看自身的实现方法)。这样,告诉对方,在约定的时间访问网址,文件就会乖乖的自动现身了,让对方自行下载即可。

第一步,sqlserver定时导出数据,用到了bcp这个功能,具体语法是

exec master..xp_cmdshell 'bcp "select * from table" queryout c:\abc.csv -c -T -t "," '

-t 指的是分隔符,默认是制表符,所以这里要指定为逗号;-T为可信任连接,这样就不需要用户名密码了;

中间的查询语句,如果是简单标准格式还好,如何涉及到join等等一些其他操作,运行bcp很容易出错,找语法错误也很麻烦,所以在这里我把我所需要运行的语句创建为一个视图view。这样我在调用bcp的时候,就可以使用简明的查询语句了。

xp_cmdshell这个功能默认是不开启的,需要在sqlserver外围应用配置器中开启。

然后创建一个存储过程,新建作业,作业的步骤就是执行存储过程,设置作业的运行时间。

1、创建视图

create view dearda_view   
as   
select distinct  cst.custid,orpt.fromcitycode,orpt.tocitycode
from REPORT..rp_orderreport orpt 
inner join CRMS..Customer cst on (cst.CustomerID=orpt.custid) 
and orpt.createtime>=''2012-07-01 00:00:00'' and orpt.createtime<=getdate()
2、创建存储过程
create proc [dbo].[dearda_proc] 
as
begin
---修改视图
exec ('alter view dearda_view  
as   
select distinct  cst.custid,orpt.fromcitycode,orpt.tocitycode
from REPORT..rp_orderreport orpt 
inner join CRMS..Customer cst on (cst.CustomerID=orpt.custid) 
and orpt.createtime>=''2012-07-01 00:00:00'' and orpt.createtime<=getdate()' )

---导出数据
declare @sql varchar(500)
declare @path varchar(500)
set @path='"D:\data\shuju.'+convert(varchar(10),getdate(),120)+'.csv"'
set @sql='bcp "select * from Report..dearda_view " queryout '+ @path +' -c -T -t ","';
exec master..xp_cmdshell @sql
end
 
 
================================================================这是分割线
看到了上面我用参数的形式替代了bcp中的语句,这样的好处是很明显的.文件名是用了函数来获取
当前的日期.
第二步,在第一步中完成了数据的导出,现在就要对数据进行压缩和复制到指定的地方;利用bat,定
时运行即可;
比如第一步中的作业是在凌晨1点运行,这个bat脚本可以设置在凌晨1点半。
脚本内容如下:
@echo off
set "date=%date:~0,4%-%date:~5,2%-%date:~8,2%"
"C:\Program Files\WinRAR\Rar.exe" -ep m d:\data\shuju.%date%.rar d:\data
\shuju.%date%.csv net use \\123.123.123.123\ipc$ "dearda" /user:dearda echo f | xcopy d:\data\shuju.%date%.rar \\123.123.123.123\d$\download\
/E /Y /S /Q
 
然后在任务计划中设置定时运行此bat,至此,所想要的数据会在指定的时间自动出现在某个目录了。
附上更为详细的存储过程,摘自网上,来源不明。
ALTER proc [dbo].[up_OMP2NUW] 
as
begin
    declare @sql varchar(500)
    declare    @path varchar(500)
    set @path='"c:\OMPNUW.ARCHIVE001S.'+CAST(datepart(year,getdate()) AS varchar)+'.00.txt"'
    set @sql='bcp "select 记录标识=1,'+
    's_appnumber 档案编号,'+
    '所属机构=''商务公司'','+
    'a.depotid 所属仓库编号,'+
    'a.rackid 所属保管架编号,'+
    'a.boxid 所属保管箱编号,'+
    '(case b.boxstate when 4 then ''1'' else ''2'' end)档案类型,'+
    '(case a.app_type when 0 then ''0'' else ''1'' end)档案类型2,'+
    '状态=4,'+
    'packagetime 归档时间'+
 ' from AppArchive.dbo.AppArch_App as a inner join AppArchive.dbo.AppArch_Box as b on a.boxid=b.boxid" queryout '+ @path +' -c -T';
    exec master..xp_cmdshell @sql
end