QQ登录

只需要一步,快速开始

 注册地址  找回密码
查看: 5113|回复: 1
打印 上一主题 下一主题

[分享]SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换

[复制链接]
字体大小: 正常 放大

2

主题

0

听众

19

积分

升级  14.74%

该用户从未签到

新人进步奖

跳转到指定楼层
1#
发表于 2005-8-8 14:22 |只看该作者 |倒序浏览
|招呼Ta 关注Ta
< >熟悉<FONT face="Times New Roman">SQL SERVER 2000</FONT>的数据库管理员都知道,其<FONT face="Times New Roman">DTS</FONT>可以进行数据的导入导出,其实,我们也可以使用<FONT face="Times New Roman">Transact-SQL</FONT>语句进行导入导出操作。在<FONT face="Times New Roman"> Transact-SQL</FONT>语句中,我们主要使用<FONT face="Times New Roman">OpenDataSource</FONT>函数、<FONT face="Times New Roman">OPENROWSET </FONT>函数,关于函数的详细说明,请参考<FONT face="Times New Roman">SQL</FONT>联机帮助。利用下述方法,可以十分容易地实现<FONT face="Times New Roman">SQL SERVER</FONT>、<FONT face="Times New Roman">ACCESS</FONT>、<FONT face="Times New Roman">EXCEL</FONT>数据转换,详细说明如下:<FONT face="Times New Roman"> </FONT></P>( {9 h5 X9 M5 e
< ><FONT face="Times New Roman"> <p></p></FONT></P>" n# o9 Z' w: s, h
< ><FONT face="Times New Roman">    </FONT>一、<FONT face="Times New Roman">SQL SERVER </FONT>和<FONT face="Times New Roman">ACCESS</FONT>的数据导入导出</P>
5 y+ C# E7 Z( q- `) r9 W  z! _< ><FONT face="Times New Roman"> <p></p></FONT></P>
* Z  F1 A  |) D( w5 T< ><FONT face="Times New Roman">    </FONT>常规的数据导入导出:使用<FONT face="Times New Roman">DTS</FONT>向导迁移你的<FONT face="Times New Roman">Access</FONT>数据到<FONT face="Times New Roman">SQL Server</FONT>,你可以使用这些步骤:</P>6 z( J# k9 y3 n7 ^5 R
< ><FONT face="Times New Roman"> <p></p></FONT></P>
& d# t2 K+ {& _% R0 q8 t" k& G  S6 ]< ><FONT face="Times New Roman">    1</FONT>在<FONT face="Times New Roman">SQL SERVER</FONT>企业管理器中的<FONT face="Times New Roman">Tools</FONT>(工具)菜单上,选择<FONT face="Times New Roman">Data Transformation</FONT></P>7 \% D' G! J, Y* I* q
< ><FONT face="Times New Roman"> <p></p></FONT></P>: }  l; F( ~. i6 S' U' y" ~
< ><FONT face="Times New Roman">    2Services</FONT>(数据转换服务),然后选择<FONT face="Times New Roman"> czdImport Data</FONT>(导入数据)。</P>: ?& R8 y2 [: A+ N+ A6 ^# B
< ><FONT face="Times New Roman"> <p></p></FONT></P>5 l$ l* H8 y, ?$ h* x  I
< ><FONT face="Times New Roman">    3</FONT>在<FONT face="Times New Roman">Choose a Data Source</FONT>(选择数据源)对话框中选择<FONT face="Times New Roman">Microsoft Access as the Source</FONT>,然后键入你的。<FONT face="Times New Roman">mdb</FONT>数据库(。<FONT face="Times New Roman">mdb</FONT>文件扩展名)的文件名或通过浏览寻找该文件。</P>
' _+ q; B4 M3 ?# e< ><FONT face="Times New Roman"> <p></p></FONT></P>
6 H: ~+ o& v( }8 d5 E" ~% Y7 B< ><FONT face="Times New Roman">    4</FONT>在<FONT face="Times New Roman">Choose a Destination</FONT>(选择目标)对话框中,选择<FONT face="Times New Roman">Microsoft OLE</FONT> <FONT face="Times New Roman">DB Prov ider for SQL</FONT> <FONT face="Times New Roman">Server</FONT>,选择数据库服务器,然后单击必要的验证方式。</P>2 A3 p- z. a8 `
< ><FONT face="Times New Roman"> <p></p></FONT></P>
% S7 I: B1 u! i< ><FONT face="Times New Roman">    5</FONT>在<FONT face="Times New Roman">Specify Table Copy</FONT>(指定表格复制)或<FONT face="Times New Roman">Query</FONT>(查询)对话框中,单击<FONT face="Times New Roman">Copy tables</FONT>(复制表格)。</P>
* M) ^: S1 B# c7 v< ><FONT face="Times New Roman"> <p></p></FONT></P>
! x- ^1 h+ K; \+ ^9 q" n< ><FONT face="Times New Roman">    6</FONT>在<FONT face="Times New Roman">Select Source Tables</FONT>(选择源表格)对话框中,单击<FONT face="Times New Roman">Select All</FONT>(全部选定)。下一步,完成。</P>+ v5 ^$ T/ m7 u- D6 V! r) [- E
< ><FONT face="Times New Roman"> <p></p></FONT></P>8 q# X. r9 `; n
< ><FONT face="Times New Roman">    Transact-SQL</FONT>语句进行导入导出:</P>. ~% i: D* q$ N" [$ P9 ?& W! N
< ><FONT face="Times New Roman"> <p></p></FONT></P>
1 ]+ E/ X& Y3 s, c- U/ y6 ^< ><FONT face="Times New Roman">    1.</FONT>在<FONT face="Times New Roman">SQL SERVER</FONT>里查询<FONT face="Times New Roman">access</FONT>数据:</P>
: m9 D- E+ `! F/ Q1 v< ><FONT face="Times New Roman"> <p></p></FONT></P>
2 V! }4 x! e1 \1 \< ><FONT face="Times New Roman">    SELECT * FROM OpenDataSource</FONT>(<FONT face="Times New Roman"> \\\'Microsoft.Jet.OLEDB.4.0\\\'</FONT>,<FONT face="Times New Roman">\\\'Data Source="c</FONT>:<FONT face="Times New Roman">\\\\DB.mdb"</FONT>;<FONT face="Times New Roman">User ID=Admin</FONT>;<FONT face="Times New Roman">assword=\\\'</FONT>)……表名</P>0 m/ |% C. R. z- d$ w7 a9 ]7 r
< ><FONT face="Times New Roman"> <p></p></FONT></P>) l% ^5 o2 K! T' I
< ><FONT face="Times New Roman">    2.</FONT>将<FONT face="Times New Roman">access</FONT>导入<FONT face="Times New Roman">SQL server</FONT>在<FONT face="Times New Roman">SQL SERVER </FONT>里运行:</P>8 x. p+ _6 d: J8 Q
< ><FONT face="Times New Roman"> <p></p></FONT></P>( L3 I, |* Y+ ]* {
< ><FONT face="Times New Roman">SELECT * INTO newtable FROM OPENDATASOURCE </FONT>(<FONT face="Times New Roman">\\\'Microsoft.Jet.OLEDB.4.0\\\'</FONT>,<FONT face="Times New Roman">\\\'Data Source="c</FONT>:<FONT face="Times New Roman">\\\\DB.mdb"</FONT>;<FONT face="Times New Roman">User ID=Admin</FONT>;<FONT face="Times New Roman">assword=\\\' </FONT>)……表名<FONT face="Times New Roman"> </FONT></P>
( J. g* p' q/ o& v9 O; d& K< ><FONT face="Times New Roman"> <p></p></FONT></P>
! W- ?, s* S2 P8 T' k" S<P ><FONT face="Times New Roman">    3.</FONT>将<FONT face="Times New Roman">SQL SERVER</FONT>表里的数据插入到<FONT face="Times New Roman">Access</FONT>表中在<FONT face="Times New Roman">SQL SERVER </FONT>里运行:</P>$ I, \  `+ ]' A& y0 R
<P ><FONT face="Times New Roman"> <p></p></FONT></P>
' q, V- D7 c4 P# @1 d# L<P ><FONT face="Times New Roman">insert into OpenDataSource</FONT>(<FONT face="Times New Roman"> \\\'Microsoft.Jet.OLEDB.4.0\\\'</FONT>,<FONT face="Times New Roman">\\\'Data Source=" c</FONT>:<FONT face="Times New Roman">\\\\DB.mdb"</FONT>;<FONT face="Times New Roman">User ID=Admin</FONT>;<FONT face="Times New Roman">Password=\\\'</FONT>)……表名<FONT face="Times New Roman"> </FONT>(列名<FONT face="Times New Roman">1</FONT>,列名<FONT face="Times New Roman">2</FONT>)<FONT face="Times New Roman"> select </FONT>列名<FONT face="Times New Roman">1</FONT>,列名<FONT face="Times New Roman">2 from sql</FONT>表<FONT face="Times New Roman"> </FONT></P>$ V2 G# j, a) D; r
<P ><FONT face="Times New Roman"> <p></p></FONT></P>0 G& n6 q9 Z8 o
<P ><FONT face="Times New Roman">    </FONT>实例:<FONT face="Times New Roman">insert into OPENROWSET</FONT>(<FONT face="Times New Roman">\\\'Microsoft.Jet.OLEDB.4.0\\\'</FONT>,<FONT face="Times New Roman">\\\'C</FONT>:<FONT face="Times New Roman">\\\\db.mdb\\\'</FONT>;<FONT face="Times New Roman">\\\'admin\\\'</FONT>;<FONT face="Times New Roman">\\\'\\\'</FONT>,<FONT face="Times New Roman"> Test</FONT>)<FONT face="Times New Roman"> select id</FONT>,<FONT face="Times New Roman">name from Test</FONT></P>2 G1 X/ {& {5 e# F+ q
<P ><FONT face="Times New Roman"> <p></p></FONT></P>* A9 e$ z* W3 r
<P ><FONT face="Times New Roman">    INSERT INTO OPENROWSET</FONT>(<FONT face="Times New Roman">\\\'Microsoft.Jet.OLEDB.4.0\\\'</FONT>,<FONT face="Times New Roman"> \\\'c</FONT>:<FONT face="Times New Roman">\\\\trade.mdb\\\'</FONT>;<FONT face="Times New Roman"> \\\'admin\\\'</FONT>;<FONT face="Times New Roman"> \\\'\\\'</FONT>,<FONT face="Times New Roman"> </FONT>表名)<FONT face="Times New Roman"> SELECT * FROM sqltablename</FONT></P>3 x. `. B8 [3 x  e" c, q7 C9 |, [
<P ><FONT face="Times New Roman"> <p></p></FONT></P>
! W# a% l+ J9 H# ]  }* ?7 C- [<P ><FONT face="Times New Roman">    </FONT>二、<FONT face="Times New Roman">SQL SERVER </FONT>和<FONT face="Times New Roman">EXCEL</FONT>的数据导入导出</P>* E0 p3 D. v" t2 A
<P ><FONT face="Times New Roman"> <p></p></FONT></P>
/ g- E) w( O( h/ J1 R( }<P ><FONT face="Times New Roman">    1</FONT>、在<FONT face="Times New Roman">SQL SERVER</FONT>里查询<FONT face="Times New Roman">Excel</FONT>数据:</P>
* w" q3 {- f  F1 V$ J- F: g6 u<P ><FONT face="Times New Roman"> <p></p></FONT></P>5 p6 |5 }# X# y5 F- I6 M
<P ><FONT face="Times New Roman">    SELECT * FROM OpenDataSource</FONT>(<FONT face="Times New Roman"> \\\'Microsoft.Jet.OLEDB.4.0\\\'</FONT>,<FONT face="Times New Roman">\\\'Data Source="c</FONT>:<FONT face="Times New Roman">\\\\book1.xls"</FONT>;<FONT face="Times New Roman">User ID=Admin</FONT>;<FONT face="Times New Roman">Password=</FONT>;<FONT face="Times New Roman">Extended properties=Excel 5.0\\\'</FONT>)……<FONT face="Times New Roman">[Sheet1$] </FONT></P>+ D; T" }3 L9 B: P
<P ><FONT face="Times New Roman"> <p></p></FONT></P>  h" A; M0 w5 |9 Z' A9 P- r
<P ><FONT face="Times New Roman">    </FONT>下面是个查询的示例,它通过用于<FONT face="Times New Roman"> Jet </FONT>的<FONT face="Times New Roman"> OLE DB </FONT>提供程序查询<FONT face="Times New Roman"> Excel </FONT>电子表格。</P>+ ?: t5 I5 O; z& W0 t
<P ><FONT face="Times New Roman"> <p></p></FONT></P>* Q+ Z, L  `3 K5 {8 w+ j' D( x
<P ><FONT face="Times New Roman">    SELECT * FROM OpenDataSource </FONT>(<FONT face="Times New Roman"> \\\'Microsoft.Jet.OLEDB.4.0\\\'</FONT>,<FONT face="Times New Roman">\\\'Data Source="c</FONT>:<FONT face="Times New Roman">\\\\Finance\\\\account.xls"</FONT>;<FONT face="Times New Roman">User ID=Admin</FONT>;<FONT face="Times New Roman">Password=</FONT>;<FONT face="Times New Roman">Extended properties=Excel 5.0\\\'</FONT>)……<FONT face="Times New Roman">xactions</FONT></P>
# v/ {" I0 H/ P* D/ h<P ><FONT face="Times New Roman"> <p></p></FONT></P>
6 \4 D9 c# i6 o- U& L<P ><FONT face="Times New Roman">    2</FONT>、将<FONT face="Times New Roman">Excel</FONT>的数据导入<FONT face="Times New Roman">SQL server </FONT>:</P>
2 ~4 Z7 Q$ O- X( A. A% V2 p<P ><FONT face="Times New Roman"> <p></p></FONT></P>, F, S6 G) i9 H8 c. f6 v
<P ><FONT face="Times New Roman">    SELECT * into newtable FROM OpenDataSource</FONT>(<FONT face="Times New Roman"> \\\'Microsoft.Jet.OLEDB.4.0\\\'</FONT>,<FONT face="Times New Roman">\\\'Data Source="c</FONT>:<FONT face="Times New Roman">\\\\book1.xls"</FONT>;<FONT face="Times New Roman">User ID=Admin</FONT>;<FONT face="Times New Roman">Password=</FONT>;<FONT face="Times New Roman">Extended properties=Excel 5.0\\\'</FONT>)……<FONT face="Times New Roman">[Sheet1$] </FONT></P>5 x# ?/ g1 m7 g/ l; z7 x
<P ><FONT face="Times New Roman"> <p></p></FONT></P>* }4 {2 ?3 R+ a  |9 {7 E' q
<P ><FONT face="Times New Roman">    </FONT>实例:<FONT face="Times New Roman">SELECT * into newtable FROM OpenDataSource</FONT>(<FONT face="Times New Roman"> \\\'Microsoft.Jet.OLEDB.4.0\\\'</FONT>,<FONT face="Times New Roman">\\\'Data Source="c</FONT>:<FONT face="Times New Roman">\\\\Finance\\\\account.xls"</FONT>;<FONT face="Times New Roman">User ID=Admin</FONT>;<FONT face="Times New Roman">Password=</FONT>;<FONT face="Times New Roman">Extended properties=Excel 5.0\\\'</FONT>)……<FONT face="Times New Roman">xactions</FONT></P>/ G  V, e2 n; j0 x
<P ><FONT face="Times New Roman"> <p></p></FONT></P>2 y  a7 D' m7 ]$ v' V/ G% T
<P ><FONT face="Times New Roman">    3</FONT>、将<FONT face="Times New Roman">SQL SERVER</FONT>中查询到的数据导成一个<FONT face="Times New Roman">Excel</FONT>文件<FONT face="Times New Roman">T-SQL</FONT>代码:</P>
# n9 b/ S" \1 M- k. h<P ><FONT face="Times New Roman"> <p></p></FONT></P>
1 A$ W! L' @9 W: h/ P- B<P ><FONT face="Times New Roman">    EXEC master</FONT>……<FONT face="Times New Roman">xp_cmdshell \\\'bcp </FONT>库名。<FONT face="Times New Roman">dbo.</FONT>表名<FONT face="Times New Roman">out c</FONT>:<FONT face="Times New Roman">\\\\Temp.xls -c -q -S"servername" -U"sa" -P""\\\'</FONT>参数:<FONT face="Times New Roman">S </FONT>是<FONT face="Times New Roman">SQL</FONT>服务器名;<FONT face="Times New Roman">U</FONT>是用户;<FONT face="Times New Roman">P</FONT>是密码说明:还可以导出文本文件等多种格式<FONT face="Times New Roman"> </FONT></P>  V' v7 P+ Y9 `- ^2 o8 O
<P ><FONT face="Times New Roman"> <p></p></FONT></P>2 S+ ?6 d9 y' E
<P ><FONT face="Times New Roman">    </FONT>实例:<FONT face="Times New Roman">EXEC master</FONT>……<FONT face="Times New Roman">xp_cmdshell \\\'bcp saletesttmp.dbo.CusAccount out c</FONT>:<FONT face="Times New Roman">\\\\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"\\\' </FONT></P>
% `# T+ n  e; ?: L% B<P ><FONT face="Times New Roman"> <p></p></FONT></P>
; y) T% t: M" P2 D2 }5 w- Y' Y<P ><FONT face="Times New Roman">    EXEC master</FONT>……<FONT face="Times New Roman">xp_cmdshell \\\'bcp "SELECT au_fname</FONT>,<FONT face="Times New Roman"> au_lname FROM pubs</FONT>……<FONT face="Times New Roman">authors ORDER BY au_lname" queryout C</FONT>:<FONT face="Times New Roman">\\\\ authors.xls -c -Sservername -Usa -Ppassword\\\'</FONT></P>
8 r0 _+ _/ x" ^4 e+ t: l<P ><FONT face="Times New Roman"> <p></p></FONT></P>6 R& Q' y# J" P
<P ><FONT face="Times New Roman">    </FONT>在<FONT face="Times New Roman">VB6</FONT>中应用<FONT face="Times New Roman">ADO</FONT>导出<FONT face="Times New Roman">EXCEL</FONT>文件代码:<FONT face="Times New Roman">Dim cn As New ADODB.Connection cn.open "Driver={SQL Server}</FONT>;<FONT face="Times New Roman">Server=WEBSVR</FONT>;<FONT face="Times New Roman">DataBase=WebMis</FONT>;<FONT face="Times New Roman">UID=sa</FONT>;<FONT face="Times New Roman">WD=123</FONT>;<FONT face="Times New Roman">" cn.execute "master</FONT>……<FONT face="Times New Roman">xp_cmdshell \\\'bcp "SELECT col1</FONT>,<FONT face="Times New Roman"> col2 FROM </FONT>库名。<FONT face="Times New Roman">dbo.</FONT>表名<FONT face="Times New Roman">" queryout E</FONT>:<FONT face="Times New Roman">\\\\DT.xls -c -Sservername -Usa -Ppassword\\\'" </FONT></P>
2 G7 G5 U/ d$ n9 j# Z0 U<P ><FONT face="Times New Roman"> <p></p></FONT></P>( U$ x7 R: j7 }- A5 W+ b
<P ><FONT face="Times New Roman">    4</FONT>、在<FONT face="Times New Roman">SQL SERVER</FONT>里往<FONT face="Times New Roman">Excel</FONT>插入数据:</P>3 N" o* x  Y' M1 U; y- f
<P ><FONT face="Times New Roman"> <p></p></FONT></P>
. r$ t) O; R. h' P0 c<P ><FONT face="Times New Roman">    insert into OpenDataSource</FONT>(<FONT face="Times New Roman"> \\\'Microsoft.Jet.OLEDB.4.0\\\'</FONT>,<FONT face="Times New Roman">\\\'Data Source="c</FONT>:<FONT face="Times New Roman">\\\\Temp.xls"</FONT>;<FONT face="Times New Roman">User ID=Admin</FONT>;<FONT face="Times New Roman">Password=</FONT>;<FONT face="Times New Roman">Extended properties=Excel 5.0\\\'</FONT>)……<FONT face="Times New Roman">table1 </FONT>(<FONT face="Times New Roman">A1</FONT>,<FONT face="Times New Roman">A2</FONT>,<FONT face="Times New Roman">A3</FONT>)<FONT face="Times New Roman"> values </FONT>(<FONT face="Times New Roman">1</FONT>,<FONT face="Times New Roman">2</FONT>,<FONT face="Times New Roman">3</FONT>)<FONT face="Times New Roman"> </FONT></P>
0 g: r* D5 \4 P( w- I<P ><FONT face="Times New Roman"> <p></p></FONT></P>
6 v- f& [. g  J% N<P ><FONT face="Times New Roman">    T-SQL</FONT>代码:<FONT face="Times New Roman">INSERT INTO OPENDATASOURCE</FONT>(<FONT face="Times New Roman">\\\'Microsoft.JET.OLEDB.4.0\\\'</FONT>,<FONT face="Times New Roman">\\\'Extended Properties=Excel 8.0</FONT>;<FONT face="Times New Roman">Data source=C</FONT>:<FONT face="Times New Roman">\\\\training\\\\inventur.xls\\\'</FONT>)……<FONT face="Times New Roman">[Filiale1$] </FONT>(<FONT face="Times New Roman">bestand</FONT>,<FONT face="Times New Roman"> produkt</FONT>)<FONT face="Times New Roman"> VALUES </FONT>(<FONT face="Times New Roman">20</FONT>,<FONT face="Times New Roman"> \\\'Test\\\'</FONT>)</P>
* _2 {2 t. Y3 y/ m% _! l<P ><FONT face="Times New Roman"> <p></p></FONT></P>$ f2 R4 p3 v0 L$ M% D  v8 w
<P ><FONT face="Times New Roman">    </FONT>总结:利用以上语句,我们可以方便地将<FONT face="Times New Roman">SQL SERVER</FONT>、<FONT face="Times New Roman">ACCESS</FONT>和<FONT face="Times New Roman">EXCEL</FONT>电子表格软件中的数据进行转换,为我们提供了极大方便!</P>
zan
转播转播0 分享淘帖0 分享分享0 收藏收藏0 支持支持0 反对反对0 微信微信
lzh0601        

2

主题

3

听众

89

积分

升级  88.42%

该用户从未签到

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册地址

qq
收缩
  • 电话咨询

  • 04714969085
fastpost

关于我们| 联系我们| 诚征英才| 对外合作| 产品服务| QQ

手机版|Archiver| |繁體中文 手机客户端  

蒙公网安备 15010502000194号

Powered by Discuz! X2.5   © 2001-2013 数学建模网-数学中国 ( 蒙ICP备14002410号-3 蒙BBS备-0002号 )     论坛法律顾问:王兆丰

GMT+8, 2026-8-5 10:58 , Processed in 0.505639 second(s), 63 queries .

回顶部