使用 OPENROWSET 将 SQL Server 导出到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/909933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server export to Excel with OPENROWSET
提问by JohnIdol
I am successfully exporting to excel with the following statement:
我使用以下语句成功导出到 excel：
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?
是否有任何标准方法可以使用此模板为 Excel 工作表指定一个新名称，以便模板永远不会被写入，或者我是否必须想出一些解决方法？
What's the best way to do this in people experience?
在人们的体验中，做到这一点的最佳方式是什么？
回答by gbn
You'd have to use dynamic SQL. OPENROWSETetc only allows literals as parameters.
您必须使用动态 SQL。OPENROWSETetc 只允许文字作为参数。
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
请记住：路径是相对于 SQL Server 运行的位置
回答by Seth Ladd
Couldn't you make a copy of your template first, then pass the copy's filename into OPENROWSET?
您不能先复制模板，然后将副本的文件名传递到 OPENROWSET 中吗？
