CODEWITHSUNDEEP
sqlsql-serverexcelexport-to-excelopenrowset
JohnIdol
JohnIdol

Reputation: 50067

SQL Server export to Excel with OPENROWSET

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?

Upvotes: 4

Views: 59227

Answers (3)

Tharindu
Tharindu

Reputation: 101

You can use a template in one location and the data file in other location. When you run the script , it will delete the old file and generates a new data file.

 EXEC xp_cmdshell 'del D:\template.xls'
 EXEC xp_cmdshell 'copy C:\template.xls D:\template.xls'

 INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
 'Excel 8.0;Database=D:\template.xls;', 
 'SELECT * FROM [SheetName$]') 
  SELECT * FROM myTable

Upvotes: 1

gbn
gbn

Reputation: 432200

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

Upvotes: 7

Seth Ladd
Seth Ladd

Reputation: 120469

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

Upvotes: 1

Related Questions