Export from SQL Server to Excel

Is it possible to export a database table or a SQL query output directly in Excel? Maybe using a stored procedure or with a SQL command? The answer is: YES!

To start, we have to create a Stored Procedure wich does the work. This SP reads table columns and data and creates an excel file with all data and also with columns.


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 -T'''
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 -T'''
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)


Then we have to invoke the SP passing it the Database name, the table* name and the excel file path (It isn't necessary to create it in advance).

EXEC proc_generate_excel_with_columns 'DB_NAME', 'TABLE_NAME','FILE_PATH'

WARNING:
In most systems, some system commands used in the SP are disabled by default. To enable them, use following commands instead of the one before:


EXEC master.dbo.sp_configure 'show advanced options', 1


RECONFIGURE


EXEC master.dbo.sp_configure 'xp_cmdshell', 1


RECONFIGURE

EXEC proc_generate_excel_with_columns 'DB_NAME', 'TABLE_NAME','FILE_PATH'

EXEC master.dbo.sp_configure 'xp_cmdshell', 0


RECONFIGURE

*TIP: We can use also a View or a Temporary Table instead of "TABLE_NAME"