Put the result of a select statement into specific sheet into Excel File

Question

How I could to put the result of a select statement into specific sheet into Excel File?

Insert into (Sheet1)ExcelFileLocation
Select col1, col2 from table1
where col1 = col2

Answer 1

You could use a "linked server" or could use OPENROWSET (ad hoc remote connection) to establish the connection to the worksheet

if you choose OPENROWSET, you need to activate it on MSSQL2005 and up:

sp_configure 'show advanced options',1
reconfigure
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

finally here's the syntax to insert into excel in specific sheet:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\ExcelFileLocation\excel_file.xls;','SELECT * FROM [Sheet1$]')
select col1, col2 from table1
 where col1 = col2

For more information, see this link here.