SQL Server使用OPENROWSET导出到Excel [英] SQL Server export to Excel with OPENROWSET
本文介绍了SQL Server使用OPENROWSET导出到Excel的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
插入到OPENROWSET('Microsoft.Jet.OLEDB.4.0 ',
'Excel 8.0; Database = C:\template.xls;',
'SELECT * FROM [SheetName $]')
select * from myTable
有没有什么标准的方式来使用这个模板,为Excel表单指定一个新名称,以便模板永远不会被写入或执行我必须想出一些解决方法?
在人们的经历中,最好的方法是什么?
解决方案
你必须使用动态SQL。 OPENROWSET
等仅允许文字作为参数。
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
')
记住:路径是相对于SQL服务器正在运行
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
这篇关于SQL Server使用OPENROWSET导出到Excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文