sql查询结果集导出Excel

 

T-SQL代码：
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:/Temp.xls -c -q -S"servername" -U"sa" -P""'
参数：S 是SQL服务器名；U是用户；P是密码
说明：还可以导出文本文件等多种格式

declare @str varchar(600)
select  @str=
'bcp   "select convert(nvarchar(2),99 )+ CAST(YEAR(trxdatetime) AS NVARCHAR)+CAST(month(trxdatetime) AS NVARCHAR)+CAST(day(trxdatetime) AS NVARCHAR)+CONVERT(varchar(2),trxdatetime,8)+substring(CONVERT(varchar(5),trxdatetime,14),4,4) + convert(nvarchar(2),1 ) + rtrim(ltrim(employeeno))  from sdt..EmployeeTransaction where empgroup=convert(nvarchar(7),2010301) and prodgroup=convert(nvarchar(7),2010301) and logdate=convert(varchar(10),getdate()-1,23) order by employeeno,trxdatetime" queryout "E:/OldFactoryEmployeeAttendance/' +
CAST(YEAR(getdate()) AS NVARCHAR)+CAST(month(getdate()) AS NVARCHAR)+CAST(day(getdate()-1) AS NVARCHAR)
+'.txt" -c -q -S"svr26" -U"SA" -P"123"'
--select @str
EXEC   master..xp_cmdshell   @str

 

--Copy 文件复到另一个目录
--remark: copy file to other server
declare @copyFile varchar(300)
set @copyFile='copy E:/OldFactoryEmployeeAttendance/'+ CAST(YEAR(getdate()) AS NVARCHAR)+CAST(month(getdate()) AS NVARCHAR)+CAST(day(getdate()-1) AS NVARCHAR) +
'.txt  //gproreport/test/'+ CAST(YEAR(getdate()) AS NVARCHAR)+CAST(month(getdate()) AS NVARCHAR)+CAST(day(getdate()-1) AS NVARCHAR) + '.txt'
--select @copyFile
exec master..xp_cmdshell  @copyFile

 

 

--注:系统定时运行可把这段sql Code加到Job