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

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

假定表如下：

USE testDb2

GO

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

from

information_schema.columns

where

table_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_path+''''

print @sql

exec(@sql)

--Delete temp File