Tuesday, June 12, 2012

Exporting table to excel - TSQL

There are many ways to export a table to excel - using SSIS/DTS package, bcp or simply right click on the grid results, copy and paste results to a new excel file - but most of them are cumbersome.

Here is an easier method which uses sp_makewebtask stored procedure. It needs to be enabled first using sp_configure.


sp_configure 'show advanced options' ,1;
GO
RECONFIGURE;
GO
sp_configure 'Web Assistant Procedures',1;
GO
RECONFIGURE
GO
Then, execute the below:
EXEC sp_makewebtask @outputfile = 'E:\testing.xls'
 ,@query = 'Select * from [RSBY-FINAL]..Tbl_policyRenewalDetails'
 ,@colheaders = 1
 ,@FixedFont = 0
 ,@lastupdated = 0
 ,@resultstitle = 'Testing details'
GO