I am successfully exporting 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 specifying a new name for the excel sheet so that the template never gets written to or do I have to come up with some work-around?
What's the best way to do this in people experience?
You'd have to use dynamic SQL. OPENROWSET
etc only 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 is relative to where SQL Server is running
Couldn't you make a copy of your template first, then pass the copy's filename into OPENROWSET?