SQL Server export to Excel with OPENROWSET

Question

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?

Solution 1

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

Solution 2

Couldn't you make a copy of your template first, then pass the copy's filename into OPENROWSET?

Comments

[ZygD]

Ah I get you. No, you can't manipulate Excel that way in T-SQL. Perhaps a CLR stored proc?

[JohnIdol]

never worked with CLR SPs - but I guess it's easy enough - any good sample to get me going?

[ZygD]

Sorry... not used them. The "SQLCLR" tag has 31 related questions.

[ZygD]

I'll clarify.. our corporate build from DB engineering says no

[Sunil Acharya]

I'm following the same but getting this error "The OLE DB provider "Microsoft.Jet.OLEDB.12.0" has not been registered." I have tried to change the OLEDB version also.