Sql 脚本导入EXCEL数据

   1  delete  excel
  2  
  3  -- 讀取EXCLE表數 
  4  SELECT  e_mail  excel  FROM   OPENDATASOURCE ( ' Microsoft.Jet.OLEDB.4.0 ' ,
  5  ' Data Source=C:\Documents and Settings\geovindu\桌面\vip平常按排工作\2010-06-18\2010-06-18.xls;Extended Properties=Excel 8.0 ' )... [ Sheet1$ ] 
  6  GO 
  7  
  8  
  9  -- 讀取EXCLE表數 
 10  SELECT  e_mail  excel  FROM   OPENDATASOURCE ( ' Microsoft.Jet.OLEDB.4.0 ' ,
 11  ' Data Source=C:\Documents and Settings\geovindu\My Documents\Visual Studio 2005\Projects\Sales\emailList.xls;Extended Properties=Excel 8.0 ' )... [ Sheet1$ ] 
 12  GO 
 13  
 14  -- 創造新表 
 15  
 16  SELECT  e_mail  INTO   excel  FROM   OPENDATASOURCE ( ' Microsoft.Jet.OLEDB.4.0 ' ,
 17  ' Data Source=C:\Documents and Settings\geovindu\My Documents\Visual Studio 2005\Projects\Sales\emailList.xls;Extended Properties=Excel 8.0 ' )... [ Sheet1$ ] 
 18  GO 
 19  
 20  -- 在原有表插入，EXCEL的單元格列頭名與表名要一致 
 21  INSERT   INTO  excel 
 22  SELECT   *   FROM   OPENDATASOURCE ( ' Microsoft.Jet.OLEDB.4.0 ' ,
 23  ' Data Source=C:\Documents and Settings\geovindu\桌面\vip平常按排工作\2010-06-18\2010-06-18.xls;Extended Properties=Excel 8.0 ' )... [ Sheet1$ ] 
 24  GO 
 25  SELECT   *   FROM  excel
 26  
 27  DELETE  excel  WHERE  e_mail  is   null 
 28  
 29  drop   table  vipEmail
 30  select  vipno,e_mail  into  vipEmail  from  excel
 31  
 32  select   *   from  vipEmail
 33  -- - 
 34  SELECT  e_mail  INTO  excel  FROM   OPENDATASOURCE ( ' Microsoft.Jet.OLEDB.4.0 ' ,
 35  ' Data Source=C:\Documents and Settings\geovindu\My Documents\Visual Studio 2005\Projects\Sales\emailList.xls;Extended Properties=Excel 8.0 ' )... [ Sheet1$ ] 
 36  
 37   
 38  
 39  select   *   from   openrowset ( ' Microsoft.Jet.OLEDB.4.0 ' ,  ' Excel 8.0;Database=C:\Documents and Settings\geovindu\My Documents\Visual Studio 2005\Projects\Sales\emailList.xls ' , ' select * from [ '' Sheet1$ '' ] ' )
 40  
 41   

 

--2005用法
--启用Ad Hoc Distributed Queries：
exec sp_configure "show advanced options",1
RECONFIGUREexec sp_configure "Ad Hoc Distributed Queries",1
reconfigure
--2005
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=F:\Javascript\mapabc\geovindu20160122 101754.xls;Extended Properties=Excel 8.0')...[geovindu$]
GOSELECT * FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source=F:\Javascript\mapabc\geovindu20160122 101754.xls;Extended properties=Excel 8.0')...geovindu$--SELECT * from openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=F:\Javascript\mapabc\geovindu20160122 101754.xls','select * from [''geovindu$'']')--使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure "Ad Hoc Distributed Queries",0
reconfigure
exec sp_configure "show advanced options",0
RECONFIGURE