SQL Server导入Excel数据
SQL读取Excel数据方法:
1.使用opendatasource
select top 10 *
from opendatasource
(
'microsoft.jet.oledb.12.0',
'data source="D:\list0326111.xls";User ID=admin;Password=;Extended properties=Excel 5.0'
)...Sheet1$;
2.使用openrowset
select top 10 *
from openrowset
(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0; Database=D:\list0326111.xls',
'select * from [Sheet1$]'
)
但是发现在执行的时候会报错,错误信息如下
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online
这个时候,就需要我们配置一下了,执行如下代码
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
这是msdn上对opendatasource和openrowset的解释:http://msdn.microsoft.com/zh-cn/library/ms179856.aspx
参考文章:http://www.cnblogs.com/oneword/archive/2009/12/14/1623586.html
如果以上方法还是行不通,可以试试下面这种方法
SQL Server有直接Import Data...的功能,右击数据库名->Tasks->Import Data... 然后根据向导对话框,一步一步进行相应的勾选,即可完成导入。
导入后会在该数据库中新增一张表dbo.Sheet1$,这时如果你要对表中的数据进行操作就方便多了。比方说根据商品的Id,将数据库中Product表的NameA更改为与Excel中对应的NameB同名。
use DataBaseA
update dbo.Product
set dbo.Product.NameA = dbo.Sheet1$.NameB
from dbo.Sheet1$ where dbo.Product.Id= dbo.Sheet1$.Id
操作完成后再将该临时表dbo.Sheet1$删除即可。