Sqlserver 读取EXCEL

1.1 启用本地读取设置

--启用
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

 

1.2 读取EXCEL文件插入到表中

SELECT * INTO MyProjRate
FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0',
'Data Source="d:\项目税率收集-2.xlsx";User ID=admin;Password=;Extended properties=Excel 5.0')...Sheet1$

 

1.3 关闭启用设置 

--使用完成后，关闭Ad Hoc Distributed Queries：
EXEC sp_configure 'Ad Hoc Distributed Queries', 0
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO