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?

  • How to select all parent objects into DataContext using single LINQ query?
  • Execute Dynamic Query with go in sql
  • How to escape wild cards in SQL Server Like Clause
  • effect of table hint on view and table used inside that view
  • Slow bulk insert for table with many indexes
  • Multiple foreign keys to a single column
  • What’s the best way to do this in people experience?

  • SSIS. Importing several xlsx files
  • Using two do loops to insert data into database properly
  • Excel into SQL Server with Microsoft.ACE.OLEDB.12.0
  • How to get a float result by dividing two integer values?
  • be careful parts of your document may include personal information that cannot be removed by the Document Inspector
  • SQL Query to Excel in C#
  • 2 Solutions collect form web for “SQL Server export to Excel with OPENROWSET”

    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?

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.