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