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 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

--2005用法 --启用Ad Hoc Distributed Queries： exec sp_configure "show advanced options",1 RECONFIGURE   exec 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$] GO   SELECT * 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