SQL Excel导入到sqlserver表

--1、数据表已经创建，Excel首行作为表头     

--启用Ad Hoc Distributed Queries：  

exec sp_configure 'show advanced options',1    

reconfigure    

exec sp_configure 'Ad Hoc Distributed Queries',1  

 reconfigure  

 

 go

  --开始查询数据  

  if object_id('temp_XXX') is not null   

drop table temp_XXX go    

 

SELECT * into temp_XXX FROM OpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=C:\Users\Administrator\AppData\Local\Temp\1\2012.xls;User ID=;Password=;Extended properties=Excel 5.0')...sheet1$  

 

--使用完成后，关闭Ad Hoc Distributed Queries：  

exec sp_configure 'Ad Hoc Distributed Queries',0  

 reconfigure    

exec sp_configure 'show advanced options',0  

 reconfigure     

 

   select * from temp_XXX;