SQL SERVER 與ACCESS、EXCEL的數(shù)據(jù)轉(zhuǎn)換
2024-08-31 00:48:31
供稿：網(wǎng)友
sql server 與access、excel的數(shù)據(jù)轉(zhuǎn)換
熟悉sql server 2000的數(shù)據(jù)庫(kù)管理員都知道，其dts可以進(jìn)行數(shù)據(jù)的導(dǎo)入導(dǎo)出，其實(shí)，我們也可以使用transact-sql語(yǔ)句進(jìn)行導(dǎo)入導(dǎo)出操作。在transact-sql語(yǔ)句中，我們主要使用opendatasource函數(shù)、openrowset 函數(shù)，關(guān)于函數(shù)的詳細(xì)說(shuō)明，請(qǐng)參考sql聯(lián)機(jī)幫助。利用下述方法，可以十分容易地實(shí)現(xiàn)sql server、access、excel數(shù)據(jù)轉(zhuǎn)換，詳細(xì)說(shuō)明如下：
一、 sql server 和access的數(shù)據(jù)導(dǎo)入導(dǎo)出
常規(guī)的數(shù)據(jù)導(dǎo)入導(dǎo)出：
使用dts向?qū)нw移你的access數(shù)據(jù)到sql server，你可以使用這些步驟:
1在sql server企業(yè)管理器中的tools（工具）菜單上，選擇data transformation
2services（數(shù)據(jù)轉(zhuǎn)換服務(wù)），然后選擇 czdimport data（導(dǎo)入數(shù)據(jù)）。
3在choose a data source（選擇數(shù)據(jù)源）對(duì)話框中選擇microsoft access as the source，然后鍵入你的.mdb數(shù)據(jù)庫(kù)(.mdb文件擴(kuò)展名)的文件名或通過(guò)瀏覽尋找該文件。
4在choose a destination（選擇目標(biāo)）對(duì)話框中，選擇microsoft ole db prov ider for sql server，選擇數(shù)據(jù)庫(kù)服務(wù)器，然后單擊必要的驗(yàn)證方式。
5在specify table copy（指定表格復(fù)制）或query（查詢）對(duì)話框中，單擊copy tables（復(fù)制表格）。
6在select source tables（選擇源表格）對(duì)話框中，單擊select all（全部選定）。下一步，完成。
transact-sql語(yǔ)句進(jìn)行導(dǎo)入導(dǎo)出：
1. 在sql server里查詢access數(shù)據(jù):
-- ======================================================
select *
from opendatasource( 'microsoft.jet.oledb.4.0',
'data source="c:/db.mdb";user id=admin;password=')...表名
-------------------------------------------------------------------------------------------------
2. 將access導(dǎo)入sql server
-- ======================================================
在sql server 里運(yùn)行:
select *
into newtable
from opendatasource ('microsoft.jet.oledb.4.0',
'data source="c:/db.mdb";user id=admin;password=' )...表名
-------------------------------------------------------------------------------------------------
3. 將sql server表里的數(shù)據(jù)插入到access表中
-- ======================================================
在sql server 里運(yùn)行：
insert into opendatasource( 'microsoft.jet.oledb.4.0',
'data source=" c:/db.mdb";user id=admin;password=')...表名
(列名1,列名2)
select 列名1,列名2 from sql表
實(shí)例：
insert into openrowset('microsoft.jet.oledb.4.0',
'c:/db.mdb';'admin';'', test)
select id,name from test
insert into openrowset('microsoft.jet.oledb.4.0', 'c:/trade.mdb'; 'admin'; '', 表名)
select *
from sqltablename
-------------------------------------------------------------------------------------------------
二、 sql server 和excel的數(shù)據(jù)導(dǎo)入導(dǎo)出
1、在sql server里查詢excel數(shù)據(jù):
-- ======================================================
select *
from opendatasource( 'microsoft.jet.oledb.4.0',
'data source="c:/book1.xls";user id=admin;password=;extended properties=excel 5.0')...[sheet1$]
下面是個(gè)查詢的示例，它通過(guò)用于 jet 的 ole db 提供程序查詢 excel 電子表格。
select *
from opendatasource ( 'microsoft.jet.oledb.4.0',
'data source="c:/finance/account.xls";user id=admin;password=;extended properties=excel 5.0')...xactions
-------------------------------------------------------------------------------------------------
2、將excel的數(shù)據(jù)導(dǎo)入sql server :
-- ======================================================
select * into newtable
from opendatasource( 'microsoft.jet.oledb.4.0',
'data source="c:/book1.xls";user id=admin;password=;extended properties=excel 5.0')...[sheet1$]
實(shí)例:
select * into newtable
from opendatasource( 'microsoft.jet.oledb.4.0',
'data source="c:/finance/account.xls";user id=admin;password=;extended properties=excel 5.0')...xactions
-------------------------------------------------------------------------------------------------
3、將sql server中查詢到的數(shù)據(jù)導(dǎo)成一個(gè)excel文件
-- ======================================================
t-sql代碼：
exec master..xp_cmdshell 'bcp 庫(kù)名.dbo.表名out c:/temp.xls -c -q -s"servername" -u"sa" -p""'
參數(shù)：s 是sql服務(wù)器名；u是用戶；p是密碼
說(shuō)明：還可以導(dǎo)出文本文件等多種格式
實(shí)例:exec master..xp_cmdshell 'bcp saletesttmp.dbo.cusaccount out c:/temp1.xls -c -q -s"pmserver" -u"sa" -p"sa"'
exec master..xp_cmdshell 'bcp "select au_fname, au_lname from pubs..authors order by au_lname" queryout c:/ authors.xls -c -sservername -usa -ppassword'
在vb6中應(yīng)用ado導(dǎo)出excel文件代碼：
dim cn as new adodb.connection
cn.open "driver={sql server};server=websvr;database=webmis;uid=sa;wd=123;"
cn.execute "master..xp_cmdshell 'bcp "select col1, col2 from 庫(kù)名.dbo.表名" queryout e:/dt.xls -c -sservername -usa -ppassword'"
-------------------------------------------------------------------------------------------------
4、在sql server里往excel插入數(shù)據(jù):
-- ======================================================
insert into opendatasource( 'microsoft.jet.oledb.4.0',
'data source="c:/temp.xls";user id=admin;password=;extended properties=excel 5.0')...table1 (a1,a2,a3) values (1,2,3)
t-sql代碼：
insert into
opendatasource('microsoft.jet.oledb.4.0',
'extended properties=excel 8.0;data source=c:/training/inventur.xls')...[filiale1$]
(bestand, produkt) values (20, 'test')
-------------------------------------------------------------------------------------------------
總結(jié)：利用以上語(yǔ)句，我們可以方便地將sql server、access和excel電子表格軟件中的數(shù)據(jù)進(jìn)行轉(zhuǎn)換，為我們提供了極大方便！
參考：
http://www.itrain.de/knowhow/sql/transfer/adhoc/
，歡迎訪問(wèn)網(wǎng)頁(yè)設(shè)計(jì)愛(ài)好者web開(kāi)發(fā)。
