Export SQL Server to Excel with OPENROWSET

Question

I have successfully exported to excel with the following statement:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\template.xls;', 'SELECT * FROM [SheetName$]') select * from myTable 

Is there any standard way to use this template that defines a new name for an excel sheet so that the template never writes or I don't have to deal with some problem?

What is the best way to do this in people?

Answer 1

You will need to use dynamic SQL. OPENROWSET etc. allows literals as parameters.

 DECLARE @myfile varchar(800) SET @myfile = 'C:\template.xls' EXEC (' insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @myfile + ';'', ''SELECT * FROM [SheetName$]'') select * from myTable ') 

Remember: the path refers to where SQL Server is running

Answer 2

Could you first make a copy of your template and then pass the copy file name to OPENROWSET?