笔记54 SQLServer和Access、Excel数据传输简单总结

 1 --SQLServer和Access、Excel数据传输简单总结
 2 --通常的可以有3种方式：1、DTS工具 2、BCP 3、分布式查询
 3 --DTS就不需要说了，因为那是图形化操作界面，很容易上手。
 4 --这里主要讲下后面两们，分别以查、增、删、改作为简单的例子：
 5 ----下面废话就不说了，直接以T-SQL的形式表现出来。
 6 --一、SQLServer和Access
 7 --1、查询Access中数据的方法：
 8 select * from OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from serv_user')
 9 --或
10 select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\DB2.mdb";User ID=Admin;Password=')...serv_user
11 --2、从SQLServer向Access写数据：
12 insert into OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from Accee表')
13 select * from SQLServer表
14 --或用BCP
15 master..xp_cmdshell'bcp "serv-htjs.dbo.serv_user" out "c:\db3.mdb" -c -q -S"." -U"sa" -P"sa"'
16 --上面的区别主要是：OpenRowSet需要mdb和表存在，BCP会在不存在的时候生成该mdb!!!
17 --3、从Access向SQLServer写数据：有了上面的基础，这个就很简单了
18 insert into SQLServer表 select * from
19 OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from Accee表')
20 --或用BCP
21 master..xp_cmdshell'bcp "serv-htjs.dbo.serv_user" in "c:\db3.mdb" -c -q -S"." -U"sa" -P"sa"'
22 --4、删除Access数据：
23 delete from OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from serv_user')
24 where lock=0
25 --5、修改Access数据：
26 update OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from serv_user')
27 set lock=1
28 --SQLServer和Access大致就这么多。
29 --二、SQLServer和Excel
30 --1、向Excel查询
31 select * from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=c:\book1.xls;','select * from [Sheet1$]') where c like '%f%'
32 select * from
33 OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
34 ,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\book1.xls',[sheet1$])
35 --1)hdr=yes时可以把xls的第1行作为字段看待，如第1个中hdr=no的话，where时就会报错
36 --2)[]和美圆$必须要，否则M$可不认这个账
37 --2、修改Execl
38 update OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\book1.xls;','select * from [Sheet1$]')
39 set a='erquan' where c like '%f%'
40 --3、导入导出
41 insert into OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\book1.xls;','select * from [Sheet2$]')(id,name)
42 select id,name from serv_user
43 --或BCP
44 master..xp_cmdshell'bcp "serv-htjs.dbo.serv_user" out "c:\book2.xls" -c -q -S"." -U"sa" -P"sa"'
45 --从Excel向SQLServer导入：
46 select * into serv_user_bak
47 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=c:\book1.xls;','select * from [Sheet1$]')
48 --如果表serv_user_bak不存在，则创建
49 
50 SELECT a.* FROM table1 a LEFT JOIN OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=192.168.0.1,2412;UID=sa;PWD=bb',DbName.dbo.table2) AS b ON a.id = b.id ORDER BY a.ID DESC
51 --将地址为192.168.0.1端口为2412的SQL SERVER上的table2表和本地服务器上的table1表联接。
52 SELECT * FROM （SELECT a.* FROM table1 a UNION SELECT b.* FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=192.168.0.1,2412;UID=aa;PWD=bb',DbName.dbo.table2) AS b) TempTable ORDER BY ID DESC
53 --将地址为192.168.0.1端口为2412的SQL SERVER上的table2表中内容加到table1表中。
54 
55 
56 --使用SQL Server 的 Microsoft OLE DB:
57 OPENROWSET（'SQLOLEDB','服务器地址,端口';'用户名';'密码', 'SQL语句')
58 --使用Jet 的 Microsoft OLE DB:
59 OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';'pwd', Orders)
60 --注意：以上两种用户名和密码前都是用分号分隔。
61 
62 
63 -- 使用 OPENROWSET 和 INNER JOIN 中的另一个表
64 --下面的示例从本地 SQL Server Northwind 数据库的 customers 表中，以及存储在相同计算机上 Access Northwind 数据库的 orders 表中选择所有数据
65 
66 --说明 下面的示例假定已经安装了 Access。
67 
68 USE pubs
69 GO
70 SELECT c.*, o.*
71 FROM Northwind.dbo.Customers AS c INNER JOIN
72    OPENROWSET('Microsoft.Jet.OLEDB.4.0',
73    'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
74    AS o
75    ON c.CustomerID = o.CustomerID
76 GO
77 
78 
79 --无密码MDB文件：
80 select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0',';database=D:\data.mdb','select * from tab')