Export to Excel from SQL Server using T-SQL script
This post helps in understanding Export to Excel from SQL Server using T-SQL Script. Typically to export data from sql server to excel we can user varius options :
- Import / Export Wizard
- BCP
- OPENROWSET
Below stored procedure will make the task easier. It provides more flexibility to Export to Excel from SQL Server using T-SQL script. .
USE MASTER
/*** Make sure that the below are enabled on your server ***/
-- Ad Hoc Remote Quires
-- XP_CMDSHELL
***/
/****** Object:StoredProcedure [dbo].[usp_ExportData_Excel]
Script Date: 10/05/2009 08:39:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*** Execution Example
EXEC [usp_ExportData_Excel] @dbName = 'Master',
@sql = 'select * from master.sys.sysdatabases',
@fullFileName = 'D:\Table_Details.xls' ,
@InstanceName ='',
@User = '',
@pwd = ''
***/
CREATE PROC [dbo].[usp_ExportData_Excel]
(
@dbName VARCHAR(100)='master',
@sql VARCHAR(8000)='select * from master.sys.sysdatabases',
@fullFileName VARCHAR(100) = 'D:\Table_Details.xls',
@InstanceName VARCHAR(100) = '',
@User VARCHAR(20) = '',
@Pwd VARCHAR(20) = ''
)
AS
BEGIN
SET NOCOUNT ON
IF @sql =''or @fullFileName =''
BEGIN
SELECT 0 AS ReturnValue -- failure
RETURN
END
-- if DB isn't passed in set it to master
SELECT @dbName ='use '+ @dbName +';'
IF object_id('##TempExportData') is not null
DROP TABLE ##TempExportData
IF object_id('##TempExportData2') is not null
DROP TABLE ##TempExportData2
-- insert data into a global temp table
DECLARE @columnNames VARCHAR(8000),
@columnConvert VARCHAR(8000),
@tempSQL VARCHAR(8000)
SELECT @tempSQL = left(@sql, charindex('from', @sql)-1) +
' into ##TempExportData '+
substring(@sql,charindex('from', @sql)-1,len(@sql))
EXEC(@dbName + @tempSQL)
IF @@error > 0
BEGIN
SELECT 0 AS ReturnValue -- failure
RETURN
END
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT @columnNames =COALESCE( @columnNames +',','')+ column_name,
@columnConvert =COALESCE( @columnConvert +',','')+'convert(nvarchar(4000),'
+ column_name + case when data_type in('datetime','smalldatetime')then',121'
WHEN data_type in('numeric','decimal')then',128'
WHEN data_type in('float','real','money','smallmoney')then',2'
WHEN data_type in('datetime','smalldatetime')then',120'
ELSE ''
END + ') as ' + column_name
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name ='##TempExportData'
-- execute select query to insert data and column names into new temp table
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from
(select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData
union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t
order by [temp##SortID]'
exec (@sql)
-- BUILD FULL BCP QUERY
DECLARE @bcpCommand VARCHAR(8000)
SET @bcpCommand ='bcp " SELECT * from ##TempExportData2" queryout'
SET @bcpCommand = @bcpCommand +' '+@fullFileName+' -S'+
@InstanceName+' -c -w -T -U '+@User+' -P '+@pwd+'","-CRAW'
EXEC master..xp_cmdshell @bcpCommand
IF @@ERROR > 0
BEGIN
SELECT 0 AS ReturnValue -- failure
RETURN
END
DROP TABLE ##TempExportData
DROP TABLE ##TempExportData2
SET @columnNames =' '
SET @columnConvert =' '
SET @tempSQL =' '
SELECT 1 as ReturnValue
END
