How to export data from SQL Server table to excel file without using SSIS

In this post we will see how we can export SQL Server table data into excel file. Here i am using xlsx if you want to work with xls then you have to change the driver in openrowset function.

I will be using dbo.ProductCategory table from Adventureworks database. First we need to create a template excel file with Column headers.

It will look like below

Now we can use below step and either create one stored procedure or create one SQL Server agent Job.

--Step 1

exec xp_cmdshell 'copy d:\temp\Product.xlsx d:\work\Product.xlsx'

--Step 2

insert into OPENROWSET(

'Microsoft.ACE.OLEDB.15.0', 

'Excel 12.0;Database=d:\work\Product.xlsx;;HDR=YES', 

'SELECT * FROM [Sheet1$]')

select * from [ProductCategory]

IF you need to work on excel (xls) then use code below

insert into OPENROWSET(

'Microsoft.Jet.OLEDB.4.0', 

        'Excel 8.0;Database=d:\work\Product.xls;;HDR=YES', 

        'SELECT * FROM [Sheet1$]')

select * from [ProductCategory]

After running this command the data will be populated in the excel as below