MSSQLSERVER导入EXCEl

Insert into test(id,name) Select idd,names FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="D:\test.xls"; User ID=;Password=;Extended properties=Excel 5.0')...[Sheet1$]

 

1)启用安全策略

exec sp_configure 'show advanced options',1 

reconfigure 

exec sp_configure 'Ad Hoc Distributed Queries',1 

reconfigure

 

2)关闭安全策略

exec sp_configure 'Ad Hoc Distributed Queries',0 

reconfigure 

exec sp_configure 'show advanced options',0 

reconfigure