1. Sample of data
------------------------------------------------------------------
-----code:start
------------------------------------------------------------------
select * from Cabang
------------------------------------------------------------------
-----code:end
------------------------------------------------------------------
Result Messages:
KdCabang Cabang
-------- ------------------------------
005 BSD
002 JAYAKARTA
003 KEBON JERUK
004 KELAPA GADING
007 SURABAYA
006 BANDUNG
001 PUSAT
008 SEMARANG
(8 row(s) affected)
2. Create this New Stored Procedure
------------------------------------------------------------------
-----code:start
------------------------------------------------------------------
CREATE procedure proc_generate_excel_with_columns
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as
--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
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 '),',',',''''')
--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'
--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)
--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)
--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)
--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)
------------------------------------------------------------------
-----code:end
------------------------------------------------------------------
Result Messages:
The command(s) completed successfully.
3. Run the stored procedure
------------------------------------------------------------------
-----code:start
------------------------------------------------------------------
--EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'
EXEC proc_generate_excel_with_columns 'HRD','Cabang','D:\Results.xls'
------------------------------------------------------------------
-----code:end
------------------------------------------------------------------
Result Messages:
output
--------------------------------------------------------
Password:
NULL
Starting copy...
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16
NULL
(8 row(s) affected)
output
--------------------------------------------------------
Password:
NULL
Starting copy...
NULL
8 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1
NULL
(8 row(s) affected)
output
--------------------------------------------------------
NULL
(1 row(s) affected)
output
--------------------------------------------------------
NULL
(1 row(s) affected)
4. Check the results,
in drive D:\, file name "Results.xls"
Source:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926