34,576
社区成员
发帖
与我相关
我的任务
分享
declare @sql varchar(5000)
declare @table1 varchar(20)
declare @table2 varchar(20)
set @table1 = 't123'
set @table2 = 't456'
set @sql='select * from '+@table1+''
exec @sql
set @sql='select * from '+@table2+''
exec @sql
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'xp_cmdshell',1
go
reconfigure
go
sp_configure 'show advance',1
go
reconfigure
go
sp_configure 'xp_cmdshell',1
go
reconfigure
go
第二步:把你要导出的数据集导出到CSV,CSV和excel是可以互转的:
use xxx
go
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'
------------------------------------------
--配置BCP命令
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM 某某库.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL