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
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