SQL Server带列名导出到Excel Export to CSV with headers 的几个思路

               

SQL Server 2008中SQL应用系列及BI学习笔记系列--目录索引

今天在项目中遇到一个问题，需要从SQL Server导出表到Excel，但需要带列名。尝试了几种方法，并小结如下：

假定表如下：

USE testDb2GOIF NOT OBJECT_ID('Demo_A') IS NULLDROP TABLE [Demo_A]/****** Object: Table [dbo].[Demo_A] downmoon:3w@live.cn ******/CREATE TABLE [dbo].[Demo_A]([ID] int not null,[Name] [Nvarchar](20) NOT NULL)GOINSERT [dbo].[Demo_A]SELECT 1,'郭靖'union ALL SELECT 2,'胡一刀'union ALL SELECT 3,'令狐冲'GO

如果通常的思路，我们可以用BCP，命令如下：

-- To allow advanced options to be changed.EXEC sp_configure 'show advanced options', 1GO-- To update the currently configured value for advanced options.RECONFIGUREGO-- To enable the feature.EXEC sp_configure 'xp_cmdshell', 1GO-- To update the currently configured value for this feature.RECONFIGUREGOEXEC master..xp_cmdshell 'bcp Testdb2.dbo.Demo_A out c:\Temp.xls -c -q -S"ap4\Net2012" -U"sa" -P"sA"' 

这样得到的xls文件中，Sheet是不带列名的。但可以改进一下，得到如下命令(参考： http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/812b8eec-5b77-42a2-bd23-965558ece5b9/ )：

方法一：使用BCP

为了方便，我创建了一个存储过程：

/****** SQL Export to xls ***************//* Example *//*CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','C:\TestExxelWithHeader.xls'*//* 2012.5.4 BY tony,邀月, 3w@live.cn */---- CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','C:\TestExcelWithHeader.xls'Create Procedure CPP_Export_To_Excel_With_Header(@db_name varchar(255),@table_name varchar(255),@file_path varchar(255))as----Generate column names as a recordsetdeclare @columns varchar(8000), @sql varchar(8000)declare @HeadersOnlyFile varchar(255),@TableDataWithoutHeaders varchar(255)set @HeadersOnlyFile=replace(cast(newid() as VARCHAR(40)),'-','')+'1.xls'set @TableDataWithoutHeaders=replace(cast(newid() as VARCHAR(40)),'-','')+'2.xls'select@columns=coalesce(@columns+',','')+column_name+' as '+column_namefrominformation_schema.columnswheretable_name=@table_nameselect @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')print @columns----Generate column names fileset @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@HeadersOnlyFile+'" -c'''print @sqlexec(@sql)----Create a dummy file to have actual dataset @sql='exec master..xp_cmdshell ''bcp "'+@db_name+'.dbo.'+@table_name+'" out "'+@TableDataWithoutHeaders+'" -c -t -T'''print @sqlexec(@sql)--Merge File into One Final Formatset @sql='exec mast