>熟悉<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>) b9 P% p; V5 R+ P" H
><FONT face="Times New Roman"> <p></p></FONT></P>) b# B S' _' ?) H1 u. z8 F
><FONT face="Times New Roman"> </FONT>一、<FONT face="Times New Roman">SQL SERVER </FONT>和<FONT face="Times New Roman">ACCESS</FONT>的数据导入导出</P>; ~# m ?, c& g& v* r" j$ [$ I
><FONT face="Times New Roman"> <p></p></FONT></P>
><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>
><FONT face="Times New Roman"> <p></p></FONT></P>
><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>
><FONT face="Times New Roman"> <p></p></FONT></P>7 x4 i; G( i0 d9 N
><FONT face="Times New Roman"> 2Services</FONT>(数据转换服务),然后选择<FONT face="Times New Roman"> czdImport Data</FONT>(导入数据)。</P>8 f, G( D, V* r' m( J7 R
><FONT face="Times New Roman"> <p></p></FONT></P>) \; V$ B3 \6 h3 e2 W' V: _
><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>8 S1 T$ C _8 x7 k
><FONT face="Times New Roman"> <p></p></FONT></P>
><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>
><FONT face="Times New Roman"> <p></p></FONT></P>7 |3 Z- W' _+ I6 o
><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>
><FONT face="Times New Roman"> <p></p></FONT></P>
><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> @" x; K J7 k z9 q
><FONT face="Times New Roman"> <p></p></FONT></P>8 {4 K- N; B) M/ B \2 ~! d2 I
><FONT face="Times New Roman"> Transact-SQL</FONT>语句进行导入导出:</P>: G! G- i6 L0 n& m4 B6 q5 O, ^
><FONT face="Times New Roman"> <p></p></FONT></P>
><FONT face="Times New Roman"> 1.</FONT>在<FONT face="Times New Roman">SQL SERVER</FONT>里查询<FONT face="Times New Roman">access</FONT>数据:</P>- |. C O1 U8 W, g- C/ N7 W) `
><FONT face="Times New Roman"> <p></p></FONT></P>- @3 f/ \, R% T5 P: r ]2 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">\\\\DB.mdb"</FONT>;<FONT face="Times New Roman">User ID=Admin</FONT>;<FONT face="Times New Roman">
assword=\\\'</FONT>)……表名</P>4 n2 J# C2 X. `7 B6 H
><FONT face="Times New Roman"> <p></p></FONT></P>5 R3 B& V; q e6 T. y
><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>
><FONT face="Times New Roman"> <p></p></FONT></P>1 T8 _0 u7 [# G7 E
><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>
><FONT face="Times New Roman"> <p></p></FONT></P>| 欢迎光临 数学建模社区-数学中国 (http://www.madio.net/) | Powered by Discuz! X2.5 |