將Access資料庫移植到SQL Server

隨著使用者對於企業級高效能資料庫的需求的增長，使用者時常要從Microsoft Access Jet引擎的檔案-伺服器環境下轉換到Microsoft SQL Server的客戶-伺服器環境。Microsoft Office 2000中的Access 2000 Upsizing Wizard可實現將資料表和查詢轉移到SQL Server 7.0中。如果您用的是Access的較早的版本，您可以先將它升級為Access 2000，然後再使用其中的Upsizing Wizard，從而將您的應用移植到SQL Server中。

如果您並不太願意採用Access 2000 和Upsizing Wizard來實現移植，本文可以作為將Access 2000移植到SQL Server的一個指南。轉移一個Access上的應用首先需要將資料轉移到SQL Server，然後將查詢轉移進資料庫，或是轉移為SQL檔案以備稍後執行。最後要採取的步驟是移植應用程式。

資料庫移植中用到的SQL Server 工具

SQL Server管理器（SQL Server Enterprise Manager）
SQL Server管理器 允許對SQL Server以及SQL Server中的對象進行企業級的配置和管理。SQL Server管理器提供一個強有力的scheduling引擎，高度的容錯力和一個嵌入式的複製管理介面。使用SQL Server管理器可以實現以下功能：

管理串連和使用者許可
建立指令碼程式
管理SQL Server對象的備份
備份資料和交易處理日誌
管理表、視圖、預存程序、觸發器、索引、規則、預設值以及使用者定義的資料類型
建立全文本索引、資料庫圖表和資料庫維護計劃
輸入和輸出資料
轉換資料
執行各種網路管理工作

在以Microsoft Windows NT為作業系統的電腦中，SQL Server Manager由SQL Server Setup進行安裝，並被預設為伺服器組件，而在運行著Windows NT 和Microsoft Windows 95的機器上，它將被預設為客戶方組件。您將從SQL Server Manager的圖形化使用者介面中啟動資料轉移服務（DTS，Data Transformation Services）。

資料轉移服務（Data Transformation Services ，DTS)
資料轉移服務允許您在多種異構資料來源之間輸入和輸出資料，這些資料來源採用基於資料庫的OLE體繫結構；或在使用SQL Server 7.0的多個電腦之間轉移資料庫和資料庫物件；您還可以通過運用資料轉移服務，更便捷地在一個線上交易處理系統（OLTP）中建立資料倉儲和資料中心。

DTS Wizard允許您互動地建立DTS包，通過OLE DB和ODBC來輸入、輸出、驗證和轉移資料。DTS Wizard還允許您在關係型資料庫之間拷貝圖解（schema）和資料。

SQL Server 查詢分析器（Query Analyzer）
SQL Server 查詢分析器是一種圖形化的查詢工具，通過它您可以分析一個查詢，同時執行多個查詢，查看資料和擷取索引建議。SQL Server 查詢分析器提供了showplan選項，可用來顯示SQL Server查詢最佳化工具所選擇的資料提取方法。

SQL Server Profiler
SQL Server Profiler可以即時地捕獲資料庫伺服器活動的連續記錄。SQL Server Profiler允許您監控SQL Server產生的事件，過濾基於使用者指定標準的事件，或將操作步驟輸出到螢幕、檔案或資料表。運用SQL Server Profiler，您可以重新執行所捕獲的上一次操作。這種工具可以輔助應用程式開發人員識別那些可能會降低應用程式效能的交易處理。在將一個基於檔案體繫結構的應用程式移植到客戶/伺服器結構中時該特性是很有用的，因為它的最後一步包括對面向新的客戶/伺服器環境的應用程式進行最佳化。

跳躍表和資料
使用DTS Wizard將您的Access資料轉移到SQL Server，可採取以下步驟：

在 SQL Server Manager（Enterprise Manager）的工具菜單中，滑鼠指向“Data Transformation Services”, 然後點擊“Import Data.”
在“選擇資料來源”（ Choose a Data Source）的交談視窗中，選擇Microsoft Access為資料來源，然後輸入您的．mdb檔案名稱（mdb為副檔名）或者選擇瀏覽檔案。
在“選擇資料目標”（Choose a Destination）的交談視窗中，選擇“Microsoft OLE DB Provider for SQL Server”，再選擇好資料庫伺服器，然後點擊所需的認證模式。
在“指定表備份或查詢”（ Specify Table Copy or Query）的交談視窗中，點擊“拷貝表”（Copy tables）。
在“選擇資料來源”的交談視窗中，點擊“選擇所有項”（ Select All）。

移植Microsoft Access查詢
您可以將Access的查詢以下面的格式之一轉移至SQL Server中：

交易處理SQL指令碼程式（Transact-SQL s cripts ）
交易處理SQL語句通常是由資料庫程式調用的，但是您也可以使用SQL Server 7.0中包含的SQL Server 查詢分析器直接運行它們。SQL Server 查詢分析器可協助開發人員測試交易處理SQL語句，或運行那些執行查詢處理、資料操作（插入，修改，刪除）和資料定義（建立表）的交易處理SQL語句。

預存程序（Stored procedures ）
開發人員可以將大部分產生自Access查詢（尋找，插入，修改，刪除）的交易處理SQL語句轉移至預存程序。用交易處理SQL語句書寫的預存程序可以用來對您的資料存取打包，並使之標準化，而且預存程序實際上是儲存在資料庫中的。預存程序可以帶參數，也可不帶參數，可以由資料庫程式調用或者由SQL Server查詢分析器手動執行。

視圖（Views ）
視圖是從一個或多個表中顯示特定的行和列的虛擬表。它們允許使用者可以不直接執行構成查詢基礎的複雜串連而建立查詢。視圖不支援參數的使用。串連多個資料表的視圖不能用INSERT, UPDATE或 DELETE語句來修改。視圖由交易處理SQL語句調用，也可用於SQL Server查詢分析器中啟動並執行程式段。SQL Server視圖和SQL-92標準不支援視圖中的ORDER BY排序子句。如欲瞭解交易處理SQL，預存程序和視圖的其他資訊，請參閱SQL Server 線上參考書。

Access查詢類型的SQL Server移植選擇與建議
一個SELECT語句可以儲存在交易處理SQL檔案、預存程序或是視圖中。建立預存程序是將資料庫應用開發與資料庫設計的物理實施分開的最佳方法。預存程序可在一處建立而由應用程式調用。
如果預存程序所基於的資料庫變化了，而預存程序經過仔細的修改以反應這些變化，則對預存程序的調用將不會受到破壞。

交叉表（CROSSTAB）
交叉表經常用於總結報表。

一個Access的交叉表可以通過SQL程式段、預存程序或視圖中的交易處理SQL語句來執行。每當發出一個查詢時，資料連線被重現執行以確保最近的資料得到使用。

根據實際應用情況，比較合適的方法是將交叉表中的資料存放區為一個暫存資料表（參考下面的MAKE TABLE），暫存資料表對資源的需求比較少，但是暫存資料表在建立的同時只提供對資料的一個快照（snapshot）。

建立表（MAKE TABLE）
Access中的“MAKE TABLE”（ 建立表）可以通過交易處理SQL指令碼程式或預存程序中的交易處理SQL語言的建表語句“CREATE TABLE”來執行。文法如下所示：
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語句可以儲存在事務_SQL指令碼程式中，然而比較好地執行UPDATE語句的方法是建立一個預存程序。

APPEND（添加）
ALLEND語句可以儲存在事務_SQL指令碼程式中，然而比較好地執行APPEND語句的方法是建立一個預存程序。

移植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查詢應執行：

開啟Access，然後在SQL Server中，開啟SQL Server查詢分析器。
在Access的資料庫視窗中點擊“Queries”tab鍵，然後點擊“Design”按鈕。
在“View”菜單上點擊“SQL”按鈕。
將整個查詢粘貼到SQL Server查詢分析器中。
測試文法，儲存交易處理SQL語句以備後用，或者在資料庫中執行這些語句。您可以選擇將交易處理SQL語句儲存到一段指令碼程式中。

移植Microsoft Access查詢到交易處理SQL語句

大部分的Access查詢應該轉換成預存程序和視圖。然而，有一些應用程式開發人員不太常用的語句可以儲存為交易處理SQL指令碼，一種以sql為副檔名的文字檔。 這些檔案可以在SQL Server查詢分析器中運行。

如果您打算將一些Access查詢轉換為sql檔案的話，可以考慮根據它們使用的方式有區別地將這些交易處理SQL語句分別放在幾個指令碼程式中。例如，您可以將必須以同樣頻率啟動並執行交易處理SQL語句歸類到同一個指令碼中。另一個指令碼中則應包含所有只在某些條件下啟動並執行交易處理SQL語句。此外，必須以一定順序執行的交易處理SQL語句應當歸類到一個不連續的指令碼中。

將Access語句轉移到交易處理SQL 檔案

將語句拷貝到SQL Server查詢分析器中
使用藍色的多選項表徵圖分析語句
在適當時候執行該語句

要執行Access中的建立表（MAKE TABLE）的查詢任務的開發人員在SQL Server中有幾種選擇。開發人員可建立下列對象之一：

一個視圖
視圖具有動態虛擬表的效果，可提供最近的資訊。這是一個輸入/輸出強化器，因為每當發出一個查詢時它都要求對資料表重現建立串連。

一個暫存資料表
暫存資料表為已串連的使用者會話建立一個快照。您可以建立局部的或全域的暫存資料表。局部暫存資料表只在當前會話中可見，而全域暫存資料表則在所有會話都是可見的。在局部暫存資料表的名字前加上單個數位首碼（(#table_name)），而在全域暫存資料表的名字前加上兩位元字的首碼(##table_name)。對暫存資料表的查詢執行起來非常快，因為它們取得一個結果集的時候通常只用一個表，而不是將多個表動態地串連在一起來。
如欲瞭解暫存資料表的其他資訊，請參閱SQL Server線上參考書。

SQL Server 7.0中的資料轉換服務（DTS）允許您通過建立包來實現暫存資料表建立的標準化、自動化和進度安排。例如，當您移植Access 2.0中的Northwind 範例資料庫時，用於季度資料報表的交叉表可轉變為一個視圖或者一個可在規範基礎上建立暫存資料表的資料轉換。如欲瞭解關於DTS的其他資訊，請參閱SQL Server線上參考書。

其他設計上的考慮
下面是當您將您的Access應用移植到SQL Server時必須考慮的一些其他問題：

使用參數
帶參數的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