通过使用openrowset来读取excel
--打开权限
exec sp_configure
'show advanced options',1
reconfigure
exec sp_configure
'Ad Hoc Distributed Queries',1
reconfigure
--查询excel信息
select * from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0','EXCEL 8.0;HDR=yes;User ID=Admin;Password=;IMEX=1;DATABASE=d:/test.xls' ,'select * from [sheet1$]')
--读取excel数据插入到SqlServer表中
INSERT INTO TABLE_NAME
select * from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0','EXCEL 8.0;HDR=yes;User ID=Admin;Password=;IMEX=1;DATABASE=d:/test.xls' ,'select * from [sheet1$]')