Code for Copying Sql Table data into Excel file

Step 1:

Before run this procedure u have to create a excel template file with the relevant column name of the table.

Step 2:

Create PROCEDURE SP_Sql_Excel @File_Name as varchar(50) = ''
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @Dos_Command varchar(1000)
    DECLARE @reportname varchar(500)
    DECLARE @Oledb_provider varchar(100)
    DECLARE @Excel_String varchar(100)

--    New File Name to be created
    IF @File_Name = ''
        Select @reportname = 'C:\temp\Excel\Template1.xls'
    ELSE
        Select @reportname = 'C:\temp\Excel\' + @File_Name + '.xls'

--    FileCopy command string formation
    SELECT @Dos_Command = 'Copy C:\temp\Excel\Template1.xls ' + @reportname

--    Execute Dos Copy command
    EXEC MASTER..XP_CMDSHELL @Dos_Command, NO_OUTPUT

--    Mentioning the OLEDB povider and excel destination filename
    set @Oledb_provider = 'Microsoft.Jet.OLEDB.4.0'
    set @Excel_String = 'Excel 8.0;Database=' + @reportname

--    Executing the OPENROWSET Command for copying the sql data  contents to Excel sheet.
exec('insert into OPENrowset (''' + @Oledb_provider + ''',''' + @Excel_String + ''',''SELECT username,password FROM [Sheet1$]'') select username,password from login')

SET NOCOUNT OFF
END



Step 3:

Run the Procedure


Exec SP_Sql_Excel 'filename'