27,579
社区成员
发帖
与我相关
我的任务
分享
--如果已建立連線,也可以如下:
EXEC master..xp_cmdshell 'bcp "SELECT * FROM 庫名.dbo.表名" queryout C:\temp.xls -c -T'
--試試以下代碼:
EXEC master..xp_cmdshell 'bcp "SELECT * FROM 庫名.dbo.表名" queryout C:\temp.xls -c -S 服務器名 -U 用戶名 -P 密碼'
--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable
select * into temtable from (select 'applicationID'as [applicationID],'Category' as [Category] --表头
union all
select CONVERT(varchar(20),[applicationID]), [Category] from #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL