如何将sql server2005的表数据导出到一个不存在的excel文件中且有列标题

[liaoba]

数据库是sql server2005,想将表中的内容导出到一个未创建的excel文件中，试了邹建大侠的存储过程，执行正确，但生成的excel打不开

[唐诗三百首]

用bcp导出,列标题单独为一行与结果集合并.

[發糞塗牆]

试试：导出成csv

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 db.dbo.tablename" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL
--create qurey result
--sp_configure 'show advance',1
--go
--reconfigure 
--go 
--sp_configure 'xp_cmdshell',1
--go
--reconfigure 
--go

[liaoba]

引用 1 楼 ap0405140 的回复:

用bcp导出,列标题单独为一行与结果集合并.

具体应该怎么写？ begin declare @filename char(15),@sql char(100) select @filename='c:\'+ convert(char(8),getdate(),112)+'.xls' select @sql='bcp "select ''编号'' 编号 union all select 编号 from pj131402..Teacher_new" queryout"' + @filename + '" -c -q -S"yhm" -U"sa" -P"test"' EXEC master..xp_cmdshell @sql end 可是执行不成功

[liaoba]

引用 2 楼 DBA_Huangzj 的回复:

试试：导出成csv

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 db.dbo.tablename" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL
--create qurey result
--sp_configure 'show advance',1
--go
--reconfigure 
--go 
--sp_configure 'xp_cmdshell',1
--go
--reconfigure 
--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'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM db.dbo.tb" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL

[唐诗三百首]

declare @filename char(15),@sql varchar(4000)

select @filename='c:\'+ convert(char(8),getdate(),112)+'.xls'

select @sql='bcp "select ''编号'' 编号 union all select rtrim(编号) from pj131402..Teacher_new" queryout "' + @filename + '" -c -q -S"yhm" -U"sa" -P"test" '

exec master..xp_cmdshell @sql

[liaoba]

5楼您好！我试了一下，提示tmptable不存在，应该先建一个表吗？表的结构应该如何？

[發糞塗牆]

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 红字部分就是动态生成表结构

[liaoba]

提示：对象名 '#temtable' 无效。[img=https://forum.csdn.net/PointForum/ui/scripts/csdn/Plugin/001/face/1.gif][/img

[發糞塗牆]

#temtable是你要导出的原数据表，你要替换

[liaoba]

db.dbo.tb这个应该写什么？也是原数据表吗？和#temtable一样吗？

[發糞塗牆]

if OBJECT_ID('temtable','u')is not null drop table temtable select * into temtable from (select 'applicationID'as [applicationID],'Category' as [Category] --这里是excel的表头 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 db.dbo.tb" queryout --需要改成你的库名.dbo.表名 C:\Temp\DBA_date\'+@filename+' -c -T -t","'--c:\tempdb这些是导出路径，按需调整 print @strSQL EXEC master..xp_cmdshell @strSQL

[liaoba]

if OBJECT_ID('temtable','u')is not null drop table temtable --这是我的代码，运行正常，但生成的excel文件还是没有标题行呀？是将数据库pj131402里的team表里的数据导出到excel，麻烦帮我看看哪里有错误呀？ select * into temtable from (select 'team_id'as [teamid],'team_name' as [team_name] union all select [team_id], [team_name] from team)a declare @filename nvarchar(30) select @filename='d:\'+ convert(char(8),getdate(),112)+'.xls' declare @strSQL nvarchar(1024) select @strsql='bcp "select team_id,team_name from pj131402..team" queryout "' + @filename + '" -c -q -S"yhm" -U"sa" -P"test"' print @strSQL EXEC master..xp_cmdshell @strSQL

[發糞塗牆]

if OBJECT_ID('temtable','u')is not null
drop table temtable
select * into temtable from (select 'team_id'as [teamid],'team_name'  as [team_name] 
union all 
select [team_id], [team_name] from  team)a
declare @filename nvarchar(30)
select @filename='d:\'+ convert(char(8),getdate(),112)+'.xls'
declare @strSQL nvarchar(1024)
select @strsql='bcp "select team_id,team_name from pj131402..temtable" queryout "' + @filename + '" -c -q -S"yhm" -U"sa" -P"test"'
print @strSQL
EXEC master..xp_cmdshell @strSQL

[_小黑_]

select * from tabel 之后 全选 结果 右键 有各种提示

[liaoba]

非常感谢！终于好用了！