sql怎么导出带标题的csv格式_SQL Server带列名导出到Excel(Export to CSV with headers)的几个思路...

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

假定表如下：

USEtestDb2GO

IF NOT OBJECT_ID('Demo_A') IS NULL

DROP 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)GO

INSERT [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', 1

GO

--To update the currently configured value for advanced options.

RECONFIGURE

GO

--To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

--To update the currently configured value for this feature.

RECONFIGURE

GO

EXEC master..xp_cmdshell 'bcp Testdb2.dbo.Demo_A out c:\Temp.xls -c -q -S"ap4\Net2012" -U"sa" -P"sA"'

方法一：使用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 ProcedureCPP_Export_To_Excel_With_Header

(@db_name varchar(255),@table_name varchar(255),@file_path varchar(255)

)as

----Generate column names as a recordset

declare @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_name

select @columns=''''''+replace(replace(@columns,'as','''''as'),',',',''''')print @columns

----Generate column names file

set @sql='exec master..xp_cmdshell''bcp " select * from (select'+@columns+') as t" queryout "'+@HeadersOnlyFile+'" -c'''

print @sql

exec(@sql)----Create a dummy file to have actual data

set @sql='exec master..xp_cmdshell''bcp "'+@db_name+'.dbo.'+@table_name+'" out "'+@TableDataWithoutHeaders+'" -c -t -T'''

print @sql

exec(@sql)--Merge File into One Final Format

set @sql='exec master..xp_cmdshell''copy /b'+@HeadersOnlyFile+'+'+@TableDataWithoutHeaders+' '+@file