將Access數(shù)據(jù)庫移植到SQL Server[摘]
2024-08-31 00:48:32
供稿：網(wǎng)友
隨著用戶對于企業(yè)級高性能數(shù)據(jù)庫的需求的增長，用戶時常要從microsoft access jet引擎的文件-服務(wù)器環(huán)境下轉(zhuǎn)換到microsoft sql server的客戶-服務(wù)器環(huán)境。microsoft office 2000中的access 2000 upsizing wizard可實現(xiàn)將數(shù)據(jù)表和查詢轉(zhuǎn)移到sql server 7.0中。如果您用的是access的較早的版本，您可以先將它升級為access 2000，然后再使用其中的upsizing wizard，從而將您的應(yīng)用移植到sql server中。
如果您并不太愿意采用access 2000 和upsizing wizard來實現(xiàn)移植，本文可以作為將access 2000移植到sql server的一個指南。轉(zhuǎn)移一個access上的應(yīng)用首先需要將數(shù)據(jù)轉(zhuǎn)移到sql server，然后將查詢轉(zhuǎn)移進(jìn)數(shù)據(jù)庫，或是轉(zhuǎn)移為sql文件以備稍后執(zhí)行。最后要采取的步驟是移植應(yīng)用程序。
數(shù)據(jù)庫移植中用到的sql server 工具
sql server管理器（sql server enterprise manager）
sql server管理器 允許對sql server以及sql server中的對象進(jìn)行企業(yè)級的配置和管理。sql server管理器提供一個強(qiáng)有力的scheduling引擎，高度的容錯力和一個嵌入式的復(fù)制管理界面。使用sql server管理器可以實現(xiàn)以下功能：
管理連接和用戶許可
創(chuàng)建腳本程序
管理sql server對象的備份
備份數(shù)據(jù)和事務(wù)處理日志
管理表、視圖、存儲過程、觸發(fā)器、索引、規(guī)則、默認(rèn)值以及用戶定義的數(shù)據(jù)類型
建立全文本索引、數(shù)據(jù)庫圖表和數(shù)據(jù)庫維護(hù)計劃
輸入和輸出數(shù)據(jù)
轉(zhuǎn)換數(shù)據(jù)
執(zhí)行各種網(wǎng)絡(luò)管理任務(wù)
在以microsoft windows nt為操作系統(tǒng)的計算機(jī)中，sql server manager由sql server setup進(jìn)行安裝，并被默認(rèn)為服務(wù)器組件，而在運(yùn)行著windows nt 和microsoft windows 95的機(jī)器上，它將被默認(rèn)為客戶方組件。您將從sql server manager的圖形用戶界面中啟動數(shù)據(jù)轉(zhuǎn)移服務(wù)（dts，data transformation services）。
數(shù)據(jù)轉(zhuǎn)移服務(wù)（data transformation services ，dts)
數(shù)據(jù)轉(zhuǎn)移服務(wù)允許您在多種異構(gòu)數(shù)據(jù)源之間輸入和輸出數(shù)據(jù)，這些數(shù)據(jù)源采用基于數(shù)據(jù)庫的ole體系結(jié)構(gòu)；或在使用sql server 7.0的多個計算機(jī)之間轉(zhuǎn)移數(shù)據(jù)庫和數(shù)據(jù)庫對象；您還可以通過運(yùn)用數(shù)據(jù)轉(zhuǎn)移服務(wù)，更便捷地在一個在線事務(wù)處理系統(tǒng)（oltp）中建立數(shù)據(jù)倉庫和數(shù)據(jù)中心。
dts wizard允許您交互地創(chuàng)建dts包，通過ole db和odbc來輸入、輸出、驗證和轉(zhuǎn)移數(shù)據(jù)。dts wizard還允許您在關(guān)系型數(shù)據(jù)庫之間拷貝圖解（schema）和數(shù)據(jù)。
sql server 查詢分析器（query analyzer）
sql server 查詢分析器是一種圖形化的查詢工具，通過它您可以分析一個查詢，同時執(zhí)行多個查詢，查看數(shù)據(jù)和獲取索引建議。sql server 查詢分析器提供了showplan選項，可用來顯示sql server查詢優(yōu)化器所選擇的數(shù)據(jù)提取方法。
sql server profiler
sql server profiler可以實時地捕獲數(shù)據(jù)庫服務(wù)器活動的連續(xù)記錄。sql server profiler允許您監(jiān)控sql server產(chǎn)生的事件，過濾基于用戶指定標(biāo)準(zhǔn)的事件，或?qū)⒉僮鞑襟E輸出到屏幕、文件或數(shù)據(jù)表。運(yùn)用sql server profiler，您可以重新執(zhí)行所捕獲的上一次操作。這種工具可以幫助應(yīng)用程序開發(fā)者識別那些可能會降低應(yīng)用程序性能的事務(wù)處理。在將一個基于文件體系結(jié)構(gòu)的應(yīng)用程序移植到客戶/服務(wù)器結(jié)構(gòu)中時該特性是很有用的，因為它的最后一步包括對面向新的客戶/服務(wù)器環(huán)境的應(yīng)用程序進(jìn)行優(yōu)化。
轉(zhuǎn)移表和數(shù)據(jù)
使用dts wizard將您的access數(shù)據(jù)轉(zhuǎn)移到sql server，可采取以下步驟：
在 sql server manager（enterprise manager）的工具菜單中，鼠標(biāo)指向“data transformation services”, 然后點(diǎn)擊“import data.”
在“選擇數(shù)據(jù)源”（ choose a data source）的對話窗口中，選擇microsoft access為數(shù)據(jù)源，然后輸入您的．mdb文件名（mdb為文件擴(kuò)展名）或者選擇瀏覽文件。
在“選擇數(shù)據(jù)目標(biāo)”（choose a destination）的對話窗口中，選擇“microsoft ole db provider for sql server”，再選擇好數(shù)據(jù)庫服務(wù)器，然后點(diǎn)擊所需的認(rèn)證模式。
在“指定表備份或查詢”（ specify table copy or query）的對話窗口中，點(diǎn)擊“拷貝表”（copy tables）。
在“選擇數(shù)據(jù)源”的對話窗口中，點(diǎn)擊“選擇所有項”（ select all）。
移植microsoft access查詢
您可以將access的查詢以下面的格式之一轉(zhuǎn)移至sql server中：
事務(wù)處理sql腳本程序（transact-sql s cripts ）
事務(wù)處理sql語句通常是由數(shù)據(jù)庫程序調(diào)用的，但是您也可以使用sql server 7.0中包含的sql server 查詢分析器直接運(yùn)行它們。sql server 查詢分析器可幫助開發(fā)者測試事務(wù)處理sql語句，或運(yùn)行那些執(zhí)行查詢處理、數(shù)據(jù)操作（插入，修改，刪除）和數(shù)據(jù)定義（創(chuàng)建表）的事務(wù)處理sql語句。
存儲過程（stored procedures ）
開發(fā)者可以將大部分產(chǎn)生自access查詢（查找，插入，修改，刪除）的事務(wù)處理sql語句轉(zhuǎn)移至存儲過程。用事務(wù)處理sql語句書寫的存儲過程可以用來對您的數(shù)據(jù)存取打包，并使之標(biāo)準(zhǔn)化，而且存儲過程實際上是存儲在數(shù)據(jù)庫中的。存儲過程可以帶參數(shù)，也可不帶參數(shù)，可以由數(shù)據(jù)庫程序調(diào)用或者由sql server查詢分析器手動執(zhí)行。
視圖（views ）
視圖是從一個或多個表中顯示特定的行和列的虛擬表。它們允許用戶可以不直接執(zhí)行構(gòu)成查詢基礎(chǔ)的復(fù)雜連接而建立查詢。視圖不支持參數(shù)的使用。連接多個數(shù)據(jù)表的視圖不能用insert, update或 delete語句來修改。視圖由事務(wù)處理sql語句調(diào)用，也可用于sql server查詢分析器中運(yùn)行的程序段。sql server視圖和sql-92標(biāo)準(zhǔn)不支持視圖中的order by排序子句。如欲了解事務(wù)處理sql，存儲過程和視圖的其他信息，請參閱sql server 在線參考書。
access查詢類型的sql server移植選擇與建議
一個select語句可以存儲在事務(wù)處理sql文件、存儲過程或是視圖中。建立存儲過程是將數(shù)據(jù)庫應(yīng)用開發(fā)與數(shù)據(jù)庫設(shè)計的物理實施分開的最佳方法。存儲過程可在一處創(chuàng)建而由應(yīng)用程序調(diào)用。
如果存儲過程所基于的數(shù)據(jù)庫變化了，而存儲過程經(jīng)過仔細(xì)的修改以反應(yīng)這些變化，則對存儲過程的調(diào)用將不會受到破壞。
交叉表（crosstab）
交叉表經(jīng)常用于總結(jié)報表。
一個access的交叉表可以通過sql程序段、存儲過程或視圖中的事務(wù)處理sql語句來執(zhí)行。每當(dāng)發(fā)出一個查詢時，數(shù)據(jù)連接被重現(xiàn)執(zhí)行以確保最近的數(shù)據(jù)得到使用。
根據(jù)實際應(yīng)用情況，比較合適的方法是將交叉表中的數(shù)據(jù)存儲為一個臨時表（參考下面的make table），臨時表對資源的需求比較少，但是臨時表在建立的同時只提供對數(shù)據(jù)的一個快照（snapshot）。
創(chuàng)建表（make table）
access中的“make table”（ 創(chuàng)建表）可以通過事務(wù)處理sql腳本程序或存儲過程中的事務(wù)處理sql語言的建表語句“create table”來執(zhí)行。語法如下所示：
select [ all | distinct ]
[ {top integer | top integer percent} [ with ties] ]
<select_list>
[ into new_table ]
[ from {<table_source>} [,…n] ]
[ where <search_condition> ]
[ group by [all] group_by_expression [,…n]
[ with { cube | rollup } ]
create table mytable (low int, high int)
update（修改）
update語句可以存儲在事務(wù)_sql腳本程序中，然而比較好地執(zhí)行update語句的方法是創(chuàng)建一個存儲過程。
append（添加）
allend語句可以存儲在事務(wù)_sql腳本程序中，然而比較好地執(zhí)行append語句的方法是創(chuàng)建一個存儲過程。
移植microsoft access的查詢到存儲過程和視圖
每個access查詢都必須用以下的一系列語句替換：
create procedure <name_here> as
< select, update, delete, insert, create table statement from microsoft access >
go
create view <name_here> as
<place (select only, with no parameters) microsoft access query>
go
對每個access查詢應(yīng)執(zhí)行：
打開access，然后在sql server中，打開sql server查詢分析器。
在access的數(shù)據(jù)庫窗口中點(diǎn)擊“queries”tab鍵，然后點(diǎn)擊“design”按鈕。
在“view”菜單上點(diǎn)擊“sql”按鈕。
將整個查詢粘貼到sql server查詢分析器中。
測試語法，保存事務(wù)處理sql語句以備后用，或者在數(shù)據(jù)庫中執(zhí)行這些語句。您可以選擇將事務(wù)處理sql語句保存到一段腳本程序中。
移植microsoft access查詢到事務(wù)處理sql語句
大部分的access查詢應(yīng)該轉(zhuǎn)換成存儲過程和視圖。然而，有一些應(yīng)用程序開發(fā)者不太常用的語句可以存儲為事務(wù)處理sql腳本，一種以sql為文件擴(kuò)展名的文本文件。 這些文件可以在sql server查詢分析器中運(yùn)行。
如果您打算將一些access查詢轉(zhuǎn)換為sql文件的話，可以考慮根據(jù)它們使用的方式有區(qū)別地將這些事務(wù)處理sql語句分別放在幾個腳本程序中。例如，您可以將必須以同樣頻率運(yùn)行的事務(wù)處理sql語句歸類到同一個腳本中。另一個腳本中則應(yīng)包含所有只在某些條件下運(yùn)行的事務(wù)處理sql語句。此外，必須以一定順序執(zhí)行的事務(wù)處理sql語句應(yīng)當(dāng)歸類到一個不連續(xù)的腳本中。
將access語句轉(zhuǎn)移到事務(wù)處理sql 文件
將語句拷貝到sql server查詢分析器中
使用藍(lán)色的多選項圖標(biāo)分析語句
在適當(dāng)時候執(zhí)行該語句
要執(zhí)行access中的創(chuàng)建表（make table）的查詢?nèi)蝿?wù)的開發(fā)者在sql server中有幾種選擇。開發(fā)者可創(chuàng)建下列對象之一：
一個視圖
視圖具有動態(tài)的虛擬表的效果，可提供最近的信息。這是一個輸入/輸出強(qiáng)化器，因為每當(dāng)發(fā)出一個查詢時它都要求對數(shù)據(jù)表重現(xiàn)建立連接。
一個臨時表
臨時表為已連接的用戶會話建立一個快照。您可以建立局部的或全局的臨時表。局部臨時表只在當(dāng)前會話中可見，而全局臨時表則在所有會話都是可見的。在局部臨時表的名字前加上單個數(shù)字的前綴（(#table_name)），而在全局臨時表的名字前加上兩位數(shù)字的前綴(##table_name)。對臨時表的查詢執(zhí)行起來非常快，因為它們?nèi)〉靡粋€結(jié)果集的時候通常只用一個表，而不是將多個表動態(tài)地連接在一起來。
如欲了解臨時表的其他信息，請參閱sql server在線參考書。
sql server 7.0中的數(shù)據(jù)轉(zhuǎn)換服務(wù)（dts）允許您通過創(chuàng)建包來實現(xiàn)臨時表建立的標(biāo)準(zhǔn)化、自動化和進(jìn)度安排。例如，當(dāng)您移植access 2.0中的northwind 范例數(shù)據(jù)庫時，用于季度數(shù)據(jù)報表的交叉表可轉(zhuǎn)變?yōu)橐粋€視圖或者一個可在規(guī)范基礎(chǔ)上建立臨時表的數(shù)據(jù)轉(zhuǎn)換。如欲了解關(guān)于dts的其他信息，請參閱sql server在線參考書。
其他設(shè)計上的考慮
下面是當(dāng)您將您的access應(yīng)用移植到sql server時必須考慮的一些其他問題：
使用參數(shù)
帶參數(shù)的sql server存儲過程需要一種不同于access查詢的語法格式，例如：
access 2.0格式：
查詢名：employee sales by country, in nwind.mdb:
parameters [beginning date] datetime, [ending date] datetime;
select orders.[order id], [last name] & ", " & [first name] as salesperson, employees.country, orders.[shipped date], [order subtotals].subtotal as [sale amount]
from employees inner join (orders inner join [order subtotals] on orders.[order id] = [order subtotals].[order id]) on employees. = orders.
where (((orders.[shipped date]) between [beginning date] and [ending date]))
order by [last name] & ", " & [first name], employees.country, orders.[shipped date];
sql server 7.0格式：
create procedure emp_sales_by_country
@beginningdate datetime,
@endingdate datetime
as
select orders.[order id], [last name] + ", " + [first name] as salesperson, employees.country,
orders.[shipped date], [order subtotals].subtotal as [sale amount]
from employees inner join (orders inner j
