首先打开安全权限:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
参考:http://blog.csdn.net/lishewen/article/details/1935986
然后用sql语句 连上excel文件,导入数据:
"insert into my_test select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=e:\\test.xls',sheet1$)"
目标表:my_test
excel文件路径:e:\\test.xls
工作空间:sheet1