24. 載入資料庫
大量複製工具程式
BULK INSERT 陳述式
資料轉換服務
Staging 資料表
SELECT...INTO 陳述式
本章總結
在學會了建立資料庫和資料庫資料表，就可學習載入資料了。將資料載入資料庫的方法有好幾種，採用何種方法取決於資料來源、對資料進行何種處理，以及資料將載入何處。本章將學習以下幾種載入資料庫的方法：
- 使用大量複製工具程式（Bulk Copy Program，BCP） ：BCP 是由 Microsoft SQL Server 2000 提供的外部程式，便於載入資料檔案至資料庫，BCP 也可以從 SQL Server 中的資料表複製資料至資料檔案中。
- 使用 BULK INSERT 指令 ：BULK INSERT T-SQL 指令可從資料檔案複製大量資料至 SQL Server 資料表中。由於此指令是 SQL 陳述式（在 ISQL、OSQL 或 Query Analyzer 中執行），處理過程將當成 SQL Server 的執行緒執行。該指令不能從 SQL Server 複製資料到資料檔案中。
- 使用資料轉換服務（Data Transformation Services，DTS） ：DTS 是一組由 SQL Server 提供的工具組，便於複製資料到 SQL Server，或從 SQL Server 複製出資料。DTS 包括匯出和匯入資料精靈。
說明
儘管 Staging 資料表並沒有提供載入資料的方法，但一般用於資料庫的載入。
每種方法都有其特點和性質，以適合使用者不同的載入需求。
說明
從備份檔復原資料庫也可以視為資料庫載入的一種形式，這點將在 第三十二章 和 第三十三章 進行討論。
BCP 或 BULK INSERT 陳述式的某些資料庫參數設定是共通的，且資料庫參數定義了 BCP 如何執行，因此必須在執行載入操作之前完成參數的設定。
下列的方法也很實用：
- SELECT...INTO 陳述式 ：用於將資料複製至另一個資料表。
- Staging 資料表 ：為暫存資料表，通常用於在資料庫中轉換資料，使資料載入過程更順利，並可於載入過程中修改資料。
載入操作的效能
本段會介紹三種常用於加強載入操作效能的設定選項。其中兩種選項會在執行大量複製操作時影響交易記錄檔，另一種則影響鎖定。使用 大量複製 的方法一次複製大量資料，可視為重建一份資料最有效率的方法。
交易記錄檔選項
SQL Server 使用複雜的交易記錄檔機制來確定資料不會因為系統故障而遺失。交易記錄檔對於系統中資料的完整性是必要的，但可能會大量的增加系統負擔。透過減少大量載入資料時的交易資料記錄量，可降低系統的負擔。
說明
發生系統故障後，SQL Server 會復原資料庫。發生故障時還未被認可的交易都會被 復原（Roll Back） （取消操作）；發生故障時已經認可的交易都會 向前復原（Roll Forward） 。復原或向前復原可以將系統復原到故障發生前的狀態。備份和復原將在 第三十二章 和 第三十三章 討論。
在預設狀態下，所有的資料庫插入操作都會被完全的記錄下來，以便在系統故障時可向前復原或復原插入的資料。藉由停用大量資料複製時的完全記錄（使用BCP、BULK INSERT 陳述式或 SELECT...INTO 陳述式），您可以減少記錄的資料量，但也使系統只允許復原操作（認可的交易就消失了）。這個選項可使大量複製效能最佳化，但是由於用於向前復原的記錄未被記錄，因此當系統發生問題時，就必須重新開始資料庫載入。
當滿足下列狀況時，大量載入操作的完全記錄就會停用：
- 資料庫選項 SELECT INTO/BULKCOPY 被設為 TRUE ，下列為使用sp_dboption預存程序的語法：
exec sp_dboption database_name, "select into/bulkcopy", TRUE
- 使用 Enterprise Manager 也可以設定該選項， 第八章 有 Enterprise Manager 的詳細介紹。
- 載入資料的目標資料表未被設定為複寫（複寫在 第二十六章 、 第二十七章 及 第二十八章 有介紹）。
- 已經指定 TABLOCK 提示（關於提示的相關資訊可參照本章 〈選擇性參數〉 一節）。如果被載入的資料表已定義了索引，就不需要指定 TABLOCK 提示。
另外，當trunc.log on chkpt資料庫選項設為 TRUE 時，會停止交易記錄檔的記錄。這也可改善大量複製的效能，但也代表當系統發生故障時，就無法執行向前復原或復原。
注意
您應該只在最初建立資料庫，並且有將大量資料載入至資料庫的需求時才將trunc.log on chkpt設為 TRUE，停止交易記錄檔的記錄會影響整個資料庫，並會導致系統在發生問題時無法復原。因此在正常操作的情況下，如果「復原」這個項目對系統很重要，就不應該使用這個選項。如果已經將trunc.log on chkpt設為 TRUE，在完成載入操作後要記得將選項在設定為 FALSE。
trunc.log on chkpt可用sp_dboption設定，語法如下：
exec sp_dboptiondatabase_name, "trunc. log on chkpt", TRUE
說明
您可以在資料庫 屬性 視窗中的 選項 標籤頁設定更多的選項，如圖 24-1 所示。 限制存取 可以限制對特定角色或單一使用者存取資料庫； 唯讀 設定不允許寫入資料庫。 預設為ANSI NULL 指定在預設狀態下，資料庫的資料行是否定義為 NULL 或 NOT NULL； 重複觸發程序 讓觸發程序可以遞迴的觸發； 自動更新統計資料 可讓 SQL Server 在查詢最佳化過程自動重建過期的統計資料； 損毀頁偵測 可偵測到不完全的分頁； 自動關閉 指定資料庫將在其資源被釋出並且所有使用者離開之後關閉； 自動壓縮 指定 SQL Server 將會週期性的壓縮資料庫檔案； 自動產生統計資料 指定查詢最佳化所需的任何遺失統計資料將在最佳化過程中自動建立； 使用引號識別項 選取這個選項指定雙引號只能用於識別項，例如資料行與資料表名稱，而字元字串必須括在單引號中。
鎖定選項
使用table lock on bulk load選項還可改善大量複製的效能，在大量複製運作模式下，這個選項以單一資料表鎖定取代多個資料列鎖定。使用sp_tableoption來設定table lock on bulk load，參數的設定如下：
exec sp_tableoption "table_name", "table lock on bulk load", TRUE
載入完成後，記得要重新設定trunc.log on chkpt的參數。由於table lock on bulk load選項只有在大量複製時，才會影響資料表的鎖定模式，所以當您沒有執行大量複製時，不會降低執行效能。
圖24-1 資料庫「屬性」視窗的「選項」標籤頁
說明
要取得table lock on bulk load選項的執行效能，您必須使用 TABLOCK 設定。
大量複製工具程式
使用 BCP，可從一個資料檔案中複製資料到 SQL Server，或是從 SQL Server 複製資料到資料檔案中。若要將資料從其他資料庫傳送到 SQL Server，BCP 也很實用。本節會學習如何使用 BCP 及其選項，並學習如何將資料格式化，以便使用 BCP 將資料匯入 SQL Server 或從 SQL Server 匯出。
BCP 語法
BCP 是指令行的執行程式，使用 BCP 需要特定的參數，而 BCP 提供許多選擇性的參數供您使用。 BCP 指令的格式如下：
bcp {[[database_name.][owner].]{table_name|view_name} | "query"} {in | out | queryout | format}data_file [-mmax_errors] [-fformat_file] [-eerror_file] [-Ffirst_row] [-Llast_row] [-bbatch_size] [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6] [-q] [-Ccode_page] [-tfield_term] [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size] [-Sserver_name[/instance_name]] [-Ulogin_id] [-Ppassword] [-T] [-v] [-R] [-k] [-E] [-h "hint[,...n]"]必要參數
必要參數指定資料的擷取和插入的位置。如之前所述，使用 BCP 可以從資料檔案擷取和插入資料到一個 SQL Server 資料表或檢視表中，或是從一個資料表（或檢視表）擷取和插入資料到資料檔案中。
在大量複製操作中所使用的資料表或檢視表，可用以下兩種方式指定：第一種方法為使用table/view_definition參數，最簡單的定義即為資料表或檢視表的名稱。如之前示範過的指令，可以指定資料表或檢視表所在的資料庫的名稱，或指定資料表或檢視表的擁有者。如果不指定資料庫名稱，資料表或檢視表所在的資料庫即為使用者登入時定義的預設資料庫， 第三十四章 會詳細討論使用者定義。
另一種方法為使用查詢指定資料表或檢視表，使用這種方法可以指定要在資料表或檢視表擷取的資料。（table/view_definition參數可用來指定用於擷取或插入資料的資料表或檢視表。）查詢必須加上雙引號，可以為加上或不加上 ORDER BY 子句的 SELECT 陳述式。指定查詢定義時，必須同時指定queryout參數（將在表 24-1 討論）。
在大量複製操作中資料檔案的位置可用data_file參數指定，但所指定的路徑必須是有效路徑。
最後，可指定一個或多個列在表 24-1 的參數
表24-1 指定大量複製的參數 參數 描述 In 從檔案夾中大量複製資料到 SQL Server 資料庫的資料表或檢視表中。 Out 從 SQL Server 資料表或檢視表擷取及大量複製資料到檔案夾中。 queryout 指定在查詢大量複製資料時，才從 SQL Server 資料庫中擷取查詢所定義的資料，當資料經擷取查詢所選出後，會大量複製到檔案夾。 Format BCP 會建立新的格式檔來執行大量複製作業，所指定的格式化選項（-n、-c、-w、-6 或 -N）和資料表或檢視表的分隔符號可用於建立格式檔。如果使用了format參數，-f 選項也必須同時指定。格式檔能讓您儲存 BCP 定義，之後當您再度使用 BCP 時，就不需要重複定義。 選擇性使用的參數
您可選擇使用列在表 24-2 的參數以修改 BCP 執行大量複製的方式。
表24-2 指定大量複製的選擇性參數 參數 描述 -a packet_size 指定在用戶端和伺服器端之間經由網路封包所傳送的位元組數目。 -b batch_size 指定每一筆批次操作所複製的資料列數，每一筆批次操作皆視為一筆交易。根據預設值，指定資料檔內的所有資料列會被當成同一筆批次操作進行複製，因此若執行大量插入資料，可能會指定此選項，以使每一筆批次操作在執行完成時，解除資料表鎖定，這樣便能執行其他處理程序（也就是使資源鎖定的時間降低）。 -c 指定 BCP 使用 char 字元資料型別來執行大量複製作業。 -e err_file 指定 BCP 錯誤檔記錄的路徑。 -f format_file 指定 BCP 所使用過的格式檔的路徑。如果 BCP 執行時使用format選項，就會建立之前所提到的格式檔（format file）。如果使用了該格式檔，就不需使用其他格式選項。 -h "hint[,...n]" 指定大量複製時所使用的提示，以下為可選擇的提示： - ORDER（column[ASC | DESC]） 指定資料行中的資料排序。
- ROWS_PER_BATCH = number 指定每個批次操作的資料列數目這個選項和 -b 類似，但不應和 -b 聯合一起使用。-b 選項將資料列指定的批次操作視為單一交易，傳送到到 SQL Server。在未指定 -b 時，整個資料檔案夾會視為單一交易，傳送到 SQL Server，並使用 ROWS_PER_BATCH 評估測量載入大小尺寸，使大量載入最佳化。
- KILOBYTES_PER_BATCH = number 指定每筆批次操作的千位元組（KB）大約數目，此選項和 -b 類似，不同之處在於此選項以千位元組 KB 為單位指定批次操作大小。
- TABLOCK 指定在大量複製載入期間使用資料表層級的鎖定。這個提示可大為提高效能，因為減少了資料表的鎖定資源競爭只在大量複製作業期間保持鎖定可降低資料表的鎖定爭論。
- CHECK_CONSTRAINTS 指定大量複製載入期間檢視條件約束。根據預設值，會忽略這些條件約束。
-i input_file 指定回應檔的名稱。回應檔中包含使用互動模式執行 BCP 時，對每個欄位的指令提示 BCP 問題的回應。 -k 指定空的資料行應使用保留 Null 值，而不使用是預設值。 -m max_errors 指定在結束 BCP 操作取消大量複製作業之前，允許發生錯誤的最多次數。如果若未包含這個選項，則預設值為 10。 -n 指定 BCP 使用 原生（native） 資料型別。 -o output_file 指定接收 BCP 輸出的輸出檔。這個輸出檔可用一般的文字檔開啟，如 Microsoft 的 Notepad 或其他工具記事本等等。 -q 指定當資料表或檢視表名稱包含非 ANSI 字元時（例如空白鍵），必須使用引號識別項雙引號。 -r row_term 指定資料列終止符號，預設值是新行字元（Newline Character，/n）。 -t field_term 指定欄位終止符號（也稱為分隔符號），預設值是 tTab 字元。 -v 列印 BCP 的版本編號和版權資訊。 -w 指定 BCP 使用 Unicode 字元執行大量複製作業。 -C code_page 指定資料檔中資料的字碼頁（Code Page）。 -E 指定在被複製的匯入的檔案中包含呈現識別 Identity 資料行的值。 -F first_row 指定大量複製操作的起始第一個資料列編號。若未指定資料列編號，第一個資料列的預設值為 1。如果要跳過在資料檔案的標題資訊，這個選項相當適合。 -L last_row 指定大量複製操作最後結束的資料列編號，預設值為 0（0 表示結束的資料列就是資料檔的最後一個資料列），表示指定資料檔的最後一個資料列。如果只想複製特定數目的資料列，這個選項相當適合。 -N 指定 BCP 對於非字元資料使用原生資料型別；對於 Unicode 字元資料使用資料的原生資料型別，而對於字元資料則使用 Unicode 來執行大量複製作業。 -P password 指定在 -U 選項中使用者帳號登入識別碼的密碼。 -R 針對用戶端系統電腦地區設定所定義的區域格式，指定使用的貨幣、日期和時間資料。 -S server_name 指定要用於載入的伺服器名稱。 -T 指定可使用的信任連線。使用該選項後，就不需使用login_id和password變數；使用網路使用者安全憑證即可。 -U login_id 指定連線至 SQL Server 時所用的使用者帳號登入識別碼。 -V 60|65|70 指定使用舊版 SQL Server 的資料型別來執行大量複製作業。使用這個選項時請與字元 -c 或 -n 選項一起使用。 -6 指定 BCP 使用 SQL Server 6.0 或 SQL Server 6.5 資料型別來執行大量複製作業。 在使用 BCP 時，您可以使用單一選項或結合數個選項指定 BCP 的執行方式，取得 BCP 強大的優點。以下我們將透過範例學習使用這些選項。
使用 BCP
本節我們會學習使用 BCP 將資料載入或輸出 SQL Server 的幾個範例。這些範例並不包含所有的 BCP 使用範圍，但足以示範 BCP 可使用的幾個模式和方法。
您可以利用之前敘述的指令行參數，或以互動式的方式使用 BCP。要在非互動模式下使用 BCP，必須指定 -n、-c、-w 或 -N 參數。如果沒有指定這些參數，BCP 會在互動模式中執行。
說明
以下所有範例將使用 Northwind 資料庫中的Customers資料表。
使用 BCP 互動模式載入資料
在互動模式下使用 BCP 載入資料有一定的難度，因為使用時要先指定資料行長度與資料型別。當您使用指令行選項時，則不需指定這些資料，相關說明會在下一節討論。在互動模式下使用 BCP 載入資料並不是很好的方法，以下將示範 BCP 如何在互動模式下運作，在範例中，我們會將 data2.file 資料檔案中的資料複製到 Northwind 資料庫的Customers資料表中，並將錯誤寫入 err.file 檔案中。您必須先建立 data2.file，該檔案包含了要載入的資料。要使用系統管理員啟動互動模式，請輸入下列指令：
bcp Northwind.dbo.Customers in data2.file -e err.file -Usa
譯註
如果出現無法連接 SQL Server 的錯誤訊息，可能是您沒有指定 SQL Server 的執行個體，請使用 -S 選項指定安裝在您機器上的 SQL Server 執行個體。
接下來會出現提示要您輸入密碼，請輸入系統管理員（sa）的密碼。
BCP 會接著要您提供欲複製資料的資訊，以下為互動階段的範例：
請輸入欄位 CustomerID [nchar] 的檔案儲存類型：char 請輸入欄位 CustomerID [1] 的前置長度：0 請輸入欄位長度 CustomerID [26]：5 請輸入欄位結束符號 [none]：, 請輸入欄位 CompanyName [nvarchar] 的檔案儲存類型：char 請輸入欄位 CompanyName [1] 的前置長度：0 請輸入欄位長度 CompanyName [189]：40 請輸入欄位結束符號 [none]：, 請輸入欄位 ContactName [nvarchar] 的檔案儲存類型：char 請輸入欄位 ContactName [1] 的前置長度：0 請輸入欄位長度 ContactName [143]：30 請輸入欄位結束符號 [none]：, 請輸入欄位 ContactTitle [nvarchar] 的檔案儲存類型：char 請輸入欄位 ContactTitle [1] 的前置長度：0 請輸入欄位長度 ContactTitle [143]：30 請輸入欄位結束符號 [none]：, 請輸入欄位 Address [nvarchar] 的檔案儲存類型：char 請輸入欄位 Address [1] 的前置長度：0 請輸入欄位長度 Address [283]：60 請輸入欄位結束符號 [none]：, 請輸入欄位 City [nvarchar] 的檔案儲存類型：char 請輸入欄位 City [1] 的前置長度：0 請輸入欄位長度 City [73]：15 請輸入欄位結束符號 [none]：, 請輸入欄位 Region [nvarchar] 的檔案儲存類型：char 請輸入欄位 Region [1] 的前置長度：0 請輸入欄位長度 Region [73]：15 請輸入欄位結束符號 [none]：, 請輸入欄位 PostalCode [nvarchar] 的檔案儲存類型：char 請輸入欄位 PostalCode [1] 的前置長度：0 請輸入欄位長度 PostalCode [49]：10 請輸入欄位結束符號 [none]：, 請輸入欄位 Country [nvarchar] 的檔案儲存類型：char 請輸入欄位 Country [1] 的前置長度：0 請輸入欄位長度 Country [73]：15 請輸入欄位結束符號 [none]：, 請輸入欄位 Phone [nvarchar] 的檔案儲存類型：char 請輸入欄位 Phone [1] 的前置長度：0 請輸入欄位長度 Phone [115]：24 請輸入欄位結束符號 [none]：, 請輸入欄位 Fax [nvarchar] 的檔案儲存類型：char 請輸入欄位 Fax [1] 的前置長度：0 請輸入欄位長度 Fax [115]：24 請輸入欄位結束符號 [none]：, 您要將這個格式資訊存成檔案？[Y/n]：Y 主檔名 [bcp.fmt]：data.fmt 開始複製… SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]在 BCP data-file 遇到非預 期的 EOF 已複製 5 個資料列。 網路封包大小（位元組）：4096 時脈時間（微秒）：總計 471
您在開始前必須了解上述這些值。由於沒有指定複製資料列的數目，所以當 BCP 執行到檔案結尾，便會出現錯誤訊息。
使用 BCP 與指令行選項載入資料
使用BCP載入資料時，使用指令行選項是較為簡單的方法，本節使用的範例將使用 BCP 從一個資料檔案載入資料，該資料檔案使用 Tab 為欄位的分隔符號，並且欄位型別為 char 資料型別。我們將使用 -c 選項指定在資料檔案中的資料為 char 格式。使用 -c 選項也使得 BCP 在非互動模式下執行。下列的指令可將在 data.file 資料檔案中 25 行的資料，複製到Customers資料表中。
bcp Northwind.dbo.Customers in data.file -e err.file -c -Usa
由於 -c 選項指定了 char 資料型別，因此不需再設定欄位長度或前置長度。假設我們在 data.file 中建立 25 列資料，並且 Tab 字元分隔的資料和Customers資料表中的資料行對應，也輸入了 sa 的密碼，該工作階段就會如下所示（視網路封包大小和時脈可能有所不同）：
開始複製… 已複製 25 個資料列。 網路封包大小（位元組）：4096 時脈時間（微秒）：總計 30
在這個範例中，複製的方向設定成In，是指資料是被載入資料庫。建議您指定一個錯誤檔案，因為錯誤檔案可以記錄在 BCP 執行期間產生的任何錯誤。如果您沒有指定錯誤檔案，錯誤會被送到螢幕上。
使用Format選項載入資料
在 〈使用 BCP〉 一節，我們已經建立一個名稱為 data.fmt 的格式檔案。您可以使用這個檔案，無須再手動輸入資料型別、前置長度、欄位長度或欄位終止符號等格式選項。使用 -f 選項就可呼叫這個檔案，呼叫方法如下：
bcp Northwind.dbo.Customers in data2.file -e err.fil -f data.fmt -L 5 -Usa
假設已經輸入sa的密碼，並且建立了 data2.file，就會看到如以下的顯示：
開始複製… 已複製 5 個資料列。 網路封包大小（位元組）：4096 時脈時間（微秒）：總計 50
除了可以使用 -f 選項，範例也包含了 -L 選項，-L 選項標示最後一個從輸入檔被複製的資料列。在本例中，-L 選項指定了第 5 個資料列為最後一筆被複製的資料列，因此消除了前面看到過的 EOF 錯誤訊息。
使用 BCP 互動模式擷取資料
在互動模式中使用 BCP 從資料庫中擷取資料，會比將資料複製到資料庫中簡單。原因是當在擷取資料時，BCP 會幫您填入欄位長度的選項。現在試著用以下的指令從Customers資料表互動地複製資料。
bcp Northwind.dbo.Customers out dataout.dat -e err.file -Usa
在輸入了這個指令及sa密碼後就會執行以下的工作階段：
請輸入欄位 CustomerID [nchar] 的檔案儲存類型：char 請輸入欄位 CustomerID [1] 的前置長度：0 請輸入欄位長度 CustomerID [26] ： 請輸入欄位結束符號 [none] ：, 請輸入欄位 CompanyName [nvarchar] 的檔案儲存類型：char 請輸入欄位 CompanyName [1] 的前置長度：0 請輸入欄位長度 CompanyName [189]： 請輸入欄位結束符號 [none]：, 請輸入欄位 ContactName [nvarchar] 的檔案儲存類型：char 請輸入欄位 ContactName [1] 的前置長度：0 請輸入欄位長度 ContactName [143]： 請輸入欄位結束符號 [none]：, 請輸入欄位 ContactTitle [nvarchar] 的檔案儲存類型：char 請輸入欄位 ContactTitle [1] 的前置長度：0 請輸入欄位長度 ContactTitle [143]： 請輸入欄位結束符號 [none]：, 請輸入欄位 Address [nvarchar] 的檔案儲存類型：char 請輸入欄位 Address [1] 的前置長度：0 請輸入欄位長度 Address [283]： 請輸入欄位結束符號 [none]：, 請輸入欄位 City [nvarchar] 的檔案儲存類型：char 請輸入欄位 City [1] 的前置長度：0 請輸入欄位長度 City [73]： 請輸入欄位結束符號 [none]：, 請輸入欄位 Region [nvarchar] 的檔案儲存類型：char 請輸入欄位 Region [1] 的前置長度：0 請輸入欄位長度 Region [73]： 請輸入欄位結束符號 [none]：, 請輸入欄位 PostalCode [nvarchar] 的檔案儲存類型：char 請輸入欄位 PostalCode [1] 的前置長度：0 請輸入欄位長度 PostalCode [49]： 請輸入欄位結束符號 [none]：, 請輸入欄位 Country [nvarchar] 的檔案儲存類型：char 請輸入欄位 Country [1] 的前置長度：0 請輸入欄位長度 Country [73]： 請輸入欄位結束符號 [none]：, 請輸入欄位 Phone [nvarchar] 的檔案儲存類型：char 請輸入欄位 Phone [1] 的前置長度：0 請輸入欄位長度 Phone [115]： 請輸入欄位結束符號 [none]：, 請輸入欄位 Fax [nvarchar] 的檔案儲存類型：char 請輸入欄位 Fax [1] 的前置長度：0 請輸入欄位長度 Fax [115]： 請輸入欄位結束符號 [none]：, 您要將這個格式資訊存成檔案？[Y/n] n 主檔名 [bcp.fmt]： 開始複製… 已複製 96 個資料列。 網路封包大小（位元組）：4096 時脈時間（微秒）：總計 20
這個互動的 BCP 工作階段建立了一個以逗點分隔欄位的檔案，該檔案為一文字檔，所有的資料皆為字元資料型別，可用記事本檢視其內容。不過，BCP 沒有在每一行的結尾加上換行字元，因此，當您使用記事本檢視資料時，資料行會很長。
使用 BCP 與選擇性選項擷取資料
要建立一個易於閱讀並且加上換行字元的資料檔案，您可以使用 -c 選項：
bcp Northwind.dbo.Customers out dataout.dat -e err.file -c -Usa
在輸入指令與sa密碼後，工作階段執行如下：
開始複製… 已複製 96 個資料列。 網路封包大小（位元組）：4096 時脈時間（微秒）：總計 10
使用queryout選項擷取資料
最後我們來看看使用queryout選項擷取資料的範例，這個選項能在複製SQL Server資料庫資料時指定查詢，指定查詢時，可選取特定的資料，並只複製被選取出來的資料。queryout的使用方法很簡單，只要在雙引號中包含查詢即可，方法如下：
bcp "SELECT CustomerID, CompanyName FROM Northwind..Customers" queryout dataout.dat -e err.file -c -Usa
輸入sa密碼後，工作階段顯示如下：
開始複製… 已複製 96 個資料列。 網路封包大小（位元組）：4096 時脈時間（微秒）：總計 1
這個查詢的輸出為 Tab 字元分隔的、並且每列有終止符號的資料檔案。該檔案的兩個欄位為：Customen ID和CompanyName。當在資料庫擷取特定的資料行或資料列時，這個方法十分有用。
BULK INSERT 陳述式
T-SQL 中的 BULK INSERT 陳述式和 BCP 差不多，兩者都可大量複製資料檔案中的資料到 SQL Server 資料庫中。然而 BCP 既可以用於複製資料也可以用於從 SQL Server 取出資料，但 BULK INSERT 只能用於資料的載入。這個限制降低了 BULK INSERT 陳述式的功能性，但是因為該陳述式可在 SQL Server 內部執行為執行緒而不是一個外部程序，所以去除了程式間的資料傳送步驟，提高了資料載入的執行效能，也使得用 BULK INSERT 陳述式載入資料比 BCP 更有效率。
BULK INSERT 語法
和 BCP 一樣，BULK INSERT 陳述式要設定幾個必要參數和幾個選擇性使用的參數。您可以使用以下的指令，在 SQL Server 內部呼叫 BULK INSERT（使用ISQL、OSQL 或 Query Analyzer），其中包含了必要及選擇性使用的參數：
BULK INSERT [[ 'database_name'.]['owner'].] { 'table_name' FROM 'data_file' } [WITH ( [BATCHSIZE [ = batch_size ]] [[ , ] CHECK_CONSTRAINTS ] [[ , ] CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]] [[ , ] DATAFILETYPE [ = { 'char' | 'native'| 'widechar' | 'widenative' }]] [[ , ] FIELDTERMINATOR [ = 'field_terminator' ]] [[ , ] FIRSTROW [ =first_row]] [[ , ] FIRETRIGGERS [ =fire_triggers]] [[ , ] FORMATFILE = 'format_file_path' ] [[ , ] KEEPIDENTITY ] [[ , ] KEEPNULLS ] [[ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ]] [[ , ] LASTROW [ = last_row ] ] [[ , ] MAXERRORS [ =max_errors]] [[ , ] ORDER({column[ ASC | DESC ]}[,...n])] [[ , ] ROWS_PER_BATCH [ =rows_per_batch]] [[ , ] ROWTERMINATOR [ = 'row_terminator' ]] [[ , ] TABLOCK ] )]必要參數
資料檔案的位置可用data_file參數指定，但所指定的路徑必須是有效路徑。
資料插入的資料庫位置由資料表定義或檢視表來決定。如前所述，您可以指定資料表或檢視表所在的資料庫名稱，或指定資料表或檢視表的擁有者。如果您試圖用 BULK INSERT 指令將資料插入檢視表，只能影響檢視表中 FROM 子句所參照底層資料表的其中一個。
選擇性使用的參數
您可以選擇性的使用列在表 24-3 的參數和關鍵字來修改 BULK INSERT 的執行，這些選項和 BCP 的選項很相似。
表24-3 BULK INSERT 選擇性使用的參數 可選擇性使用的參數 描述 BATCHSIZE =size 指定每一筆批次操作中的列數，每筆批次操作皆視為一次交易。 CHECK_CONSTRAINTS 指定檢查條件約束限制，根據預設值，會忽略這些限制。 CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]
指定資料檔中的字碼頁，只有在資料含有char、varchar或text資料型別時，CODEPAGE 才是有用意的。 DATAFILETYPE [ = 'char' | 'native' | 'widechar' | 'widenative' ]
指定資料檔中的資料型別，預設值的型別為char，其他的選項還包括native（原生資料型別）、widenative（Unicode 字元）和widenative（和 native 原生資料型別相同，差別在char、varchar和text是以 Unicode 儲存）。 FIELDTERMINATOR [ =field_term] 指定要用於char與widechar資料型別檔中的欄位終止符號端子，預設值是/t（Tab 字元）。 FIRSTROW [ =first_row] 複製的第一個資料列編號，預設值為 1，表示指定資料檔案中的第一個資料列。如果要跳過在資料檔案中的標題資訊，就可使用此參數。 FORMATFILE [ =format_file] 指定格式檔的路徑。 KEEPIDENTITY 指定保留在匯入的資料檔案中呈現 Identity 識別資料行的值。 KEEPNULLS 指定空的資料行應保留 Null 值。 KILOBYTES_PER_BATCH [ =number] 指定在大量複製時，每一筆批次操作的千位組（KB）近似資料數。 LASTROW [ =last_row] 指定大量複製時，插入的最後一個資料列編號。預設值為 0，表示指定資料檔案的最後一個資料列。如果只想複製特定的資料列，可以使用這個選項。 MAXERRORS [ =max_errors] 指定在大量複製插入結束之前，允許發生錯誤的最多次數，預設值為 10。 ORDER (column[ASC | DESC] ) 指定資料在資料檔中的排序方式。 ROWS_PER_BATCH [ =rows_per_batch]] 指定每一筆批次操作的資料列數目，每一筆批次會被視為單一交易複製。根據預值，所有在資料檔案夾的資料列，會被視為單一批次操作插入，執行一次認可。當您執行大量插入時，可考慮指定這個選項，以在執行批次操作時，能解除資料表鎖定，允許執行其他處理程序步驟。 ROWTERMINATOR [ =row_term] 要用於char與widechar資料型別檔的資料列終止符號端子，預設值是新增行字元（/n）。 使用 BULK INSERT
現在來看看使用 BULK INSERT 陳述式的兩個範例，在這兩個範例中，我們將會從data.file載入資料至 Northwind 資料庫的Customers資料表。
說明
BULK INSERT 陳述式只可用於將資料載入資料庫，不可用於擷取資料。由於BULK INSERT 的操作模式不如 BCP 來的多，所以這裡的範例會比較少。
使用下列的 T-SQL 陳述式，將資料載入資料庫：
BULK INSERT Northwind..Customers FROM 'C:/data.file' WITH ( DATAFILETYPE = 'char' ) GO您可依需要加入更多的選項，以下的範例使用更多可選擇的參數：
BULK INSERT Northwind..Customers FROM 'C:/data.file' WITH ( BATCHSIZE = 5, CHECK_CONSTRAINTS, DATAFILETYPE = 'char', FIELDTERMINATOR = '/t', FIRSTROW = 5, LASTROW = 20, TABLOCK )這個陳述式只會從資料檔載入第 5 到第 20 個資料列。不管是不是預設值，欄位終止符號應指定為 Tab 字元。該範例也檢測大量插入過程中限制條件，並在載入過程使用資料表鎖定，執行載入的交易將每 5 個資料列視為一個批次單位。
資料轉換服務
資料轉換服務（Data Transformation Services，DTS） 是 SQL Server Enterprise Manager 的一部份，讓您可以輕鬆將資料匯入資料庫或將資料從資料庫匯出。DTS 由兩種精靈組成，包括 匯出精靈 和 匯入精靈 。本節將討論如何使用這兩種精靈。
匯入精靈
您可以透過使用匯入精靈，將不同來源的資料匯入資料庫。匯入精靈和 BCP 與 BULK INSERT 陳述式不同的地方在於，匯入精靈可匯入除了資料檔案外其他來源的資料，以下為使用匯入精靈的步驟：
-
- 在 Enterprise Manager 中，展開一個伺服器群組，並且按一下想將資料匯入的伺服器。從 工具 功能表，選擇 精靈 ，然後展開 資料轉換服務 資料夾，選擇 DTS 匯入精靈 ，按一下 確定 。將出現 資料轉換服務 ─ 匯入／匯出精靈 畫面，如圖 24-2 所示。
圖24-2 「資料轉換服務─匯入／匯出精靈」畫面 - 按一下 下一步 以顯示 選擇資料來源 畫面，如圖 24-3 所示。
圖24-3 「選擇資料來源」畫面 在這裡您可以從下拉式清單中選擇資料來源。我們選擇了圖 24-3 中的 Text File 選項，您也可從下列的資料來源選項中選擇：
-
- dBase
- Microsoft Access
- Microsoft Data Link
- Microsoft Excel
- Microsoft Visual FoxPro
- Other（ODBC data source）
- Other OLE_DB data source
- Paradox
- Data files
- dBase
選項的決定有部分是取決於您在安裝系統時所安裝的 ODBC 驅動程式，舉例來說，如果安裝了 Oracle ODBC 的驅動程式，清單中就會列出這個選項， 選擇資料來源 畫面會依照您所選擇的資料來源而改變，無論選擇了哪個資料來源，都需要輸入檔案或登入資訊。
-
- 按一下 下一步 以顯示 選擇檔案格式 畫面，如圖 24-4 所示。這個畫面只有在您選擇 Text File 時才會出現，該畫面允許您選擇檔案格式，畫面中的其他選項如下：
- 使用分隔符號 或 使用固定欄位 允許您選擇匯入檔案的格式，以及特定的分隔字元或欄位寬度。
- 檔案類型 下拉式清單允許您指定匯入檔案格式為 ANSI、OEM 或 Unicode。
- 資料列分隔符號 下拉式清單允許您指定匯入檔案中用什麼符號來結束每一個資料列。
- 文字定位項 下拉式清單用來指定哪一個字元標記要用於分隔的資料檔來限定文字。
- 起始列號 指定開始複製的資料列編號（從檔案開頭為 0 號算起）。
- 第一列有資料行名稱 指定文字檔的第一列為資料行標題而非資料。
圖24-4 「選擇檔案格式」畫面 - 使用分隔符號 或 使用固定欄位 允許您選擇匯入檔案的格式，以及特定的分隔字元或欄位寬度。
- 選擇 使用分隔符號 ，以 {CR}{LF} 作為資料列分隔符號，將 文字定位項 設成 <無> 。按一下 下一步 以顯示 指定資料行分隔符號 畫面，如圖 24-5 所示。該畫面是一個指定欄位分隔符號的簡便方法，因為您會依所選擇的選項看到選擇該選項後即時的畫面，以決定是否選擇了適當的分隔符號。您可以使用 逗號 、 Tab鍵 、 分號 或 其他 以選擇分隔符號。當您選擇分隔符號時，資料列會在預覽視窗中顯示。如果您選擇的是 使用固定欄位 而不是 使用分隔符號 ，出現的會是 固定欄位的資料行位置 畫面。
圖24-5 「指定資料行分隔符號」畫面
- 在 Enterprise Manager 中，展開一個伺服器群組，並且按一下想將資料匯入的伺服器。從 工具 功能表，選擇 精靈 ，然後展開 資料轉換服務 資料夾，選擇 DTS 匯入精靈 ，按一下 確定 。將出現 資料轉換服務 ─ 匯入／匯出精靈 畫面，如圖 24-2 所示。
- 在您選取了分隔符號後，按一下 下一步 進入 選擇目的地 畫面，如圖 24-6 所示，在這個畫面之下可選擇將資料匯入的資料庫，您必須指定 SQL Server ODBC 目的地（資料庫的 ODBC 別名）、伺服器與資料庫。本範例中我們會指定 Northwind 資料庫，在這個畫面中選擇是否使用 Windows 或 SQL Server 的身份驗證。如果您使用 SQL Server 身份驗證，必須在空格中輸入 SQL Server 使用者名稱和密碼，SQL Server 安全性在 第三十四章 會作詳細說明。如果提供的使用者名稱或密碼無效，可以按一下 重新整理 按鈕重試。要變更其他的屬性，例如安全性選項、連線逾時等等，按一下 進階 按鈕修改，不過這些屬性通常不需要更改。
- 按一下 下一步 以顯示 選取來源資料表和檢視表 畫面，如圖 24-7 所示。在這個畫面中，您可以從 資料表和檢視表 中的 目的地 欄位，呼叫出下拉式清單，選擇資料要匯入的資料表。在這裡按一下 預覽 按鈕，可以預覽資料。 全選 和 全部不選 按鈕可使您選擇全部的資料表或都不選擇。
圖24-6 「選擇目的地」畫面
圖24-7 「選取來源資料表和檢視表」畫面 - 在相同的畫面中，您可以存取轉換服務，此選項可在執行資料匯入時，允許資料間的轉換（更改資料行等）。要執行這個項目，按一下 轉換 按鈕（ 轉換 標題下有三個點的那個按鈕），進入 資料行對應與轉換 對話方塊，如圖 24-8 所示。在 資料行對應 標籤頁中，可以選擇 建立目的資料表 、 刪除目的表中的資料表或資料列 、 附加資料列到目的資料表 ，預設為 附加資料列到目的資料表 。如果選擇 建立目的資料表 ，可以使用 編輯SQL 按鈕檢視及修改用於建立資料表的 SQL 陳述式。
圖24-8 「資料行對應與轉換」對話方塊內的「資料行對應」標籤頁 - 選擇 轉換 標籤頁檢視轉換選項，如圖 24-9 所示。在此標籤頁中，可選擇 直接將來源資料行複製到目的資料行 或 在複製到目的地時進行資訊轉換 。 轉換服務 的精確度轉換可以在這裡指定（如從 16-bit 到 32-bit；從 32-bit 到 16-bit）。NULL 值的轉換也可在這裡執行（如從 NOT NULL 到 NULL；從 NULL 到 NOT NULL）。
圖24-9 「資料行對應與轉換」對話方塊內的「轉換」標籤頁 - 按一下 確定 結束對話方塊，接著按一下 下一步 進入 儲存、排程和複製封裝 畫面（如圖 24-10 所示），這個畫面可以決定是否現在就執行匯入，還是要安排匯入的排程。在這裡也可選擇 儲存 DTS 封裝 以便日後再次執行此匯入程序。要選擇這個項目，請按一下 儲存 DTS 封裝 核取方塊（顯示在下方的 儲存 區域中），選取後就可儲存之前選取過的所有轉換服務設定。在這裡我們選擇 立即執行 。
- 按一下 下一步 進入 正在完成 DTS 匯入／匯出精靈 （如圖 24-11 所示），按一下 完成 執行匯入。
圖24-10 「儲存、排程和複製封裝」畫面
圖24-11 「正在完成 DTS 匯入／匯出精靈」畫面 - 按一下 完成 後，會看到 正在執行封裝 畫面的執行（如圖24-12），執行完成後會出現一個訊息，告知您匯入完成或產生錯誤。
圖24-12 「正在執行封裝」畫面
使用 DTS 匯入資料很簡單，但是若需要重複執行資料的匯入，建立程式碼來匯入會更有效率。您可以使用 BULK INSERT 陳述式儲存在一個.sql檔案內的方式，建立一個指令碼檔案。
匯出精靈
使用 匯出精靈 可將資料從資料庫中匯出到外部的目的地。 匯出精靈 和 BCP 不同，可以將資料匯出到除了資料檔案以外的目的地。以下為使用 匯出精靈 的步驟：
-
- 在 Enterprise Manager 中，展開一個伺服器群組，並且選擇想將資料匯出的伺服器。從 工具 功能表中，選擇 精靈 ，然後展開 資料轉換服務 資料夾，選擇 DTS 匯出精靈 ，按一下 確定 ，會出現 資料轉換服務 ─ 匯入／匯出精靈 畫面，如圖 24-13 所示。
- 按一下 下一步 進入 選擇資料來源 畫面，如圖 24-14 所示。在這裡可以指定資料來源，您可以保留預設值 Microsoft OLE DB Provider For SQL Server 這個設定，或是選擇 Microsoft ODBC Driver For SQL Server 。這兩項選擇都可以和 SQL Server 連線。其他的選項可以用於從其他種類的資料庫匯出資料。選擇一個資料庫繼續匯出步驟，在本例中我們選擇 Northwind 資料庫。您可以按 進階 按鈕更改進階的選項，如連線逾時、網路位置、網路程式庫和工作站 ID 等，不過這些屬性通常不需要更改。
圖24-13 「資料轉換服務─匯入／匯出精靈」畫面
圖24-14 「選擇資料來源」畫面 - 按一下 下一步 進入 選擇目的地 畫面，如圖 24-15 所示。這個畫面會依照選擇的目的地而有所不同，但在大部分的情況下，會要求您輸入登入及檔案資訊。在本例中我們選擇 Text File 為目的地，省略輸入登入及檔案資訊的要求，並將資料表儲存成文字檔。在 檔案名稱 文字方塊中輸入目的地檔案名稱。
圖24-15 「選擇目的地」畫面 - 按一下 下一步 進入 指定資料表複製或查詢 畫面，如圖 24-16 所示。這個畫面可指定是否匯出整個資料表，還是利用查詢匯出部分資料表的資料。如果您是選擇了其他的 SQL Server 資料庫為輸出目的地，那麼 在 SQL Server 資料庫之間進行物件和資料的複製 這個選項就可以使用。
圖24-16 「指定資料表複製或查詢」畫面 - 如果選擇 使用查詢來指定傳送的資料 後按一下 下一步 的話，會出現 鍵入 SQL 陳述式 畫面，如圖 24-17 所示。這裡可鍵入 SQL 陳述式，只選出您想匯出的資料。查詢可用於選擇資料行或資料列的子集，或是選擇整個資料表。
圖24-17 「鍵入 SQL 陳述式」畫面 - 選擇 下一步 進入 選擇目的檔案格式 畫面，如圖 24-18 所示。（如果您是從 鍵入 SQL Server 陳述式 進入本畫面，在 來源 則不會顯示下拉式清單。）在這個畫面中可以替目的地檔案指定數個格式選項，包括檔案是否使用分隔符號或固定欄位。選定格式後按一下 下一步 。
如果您選擇的是 複製來源資料庫的資料表和檢視表 ，按一下 下一步 同樣會進入 選擇目的檔案格式 畫面，以這種方法進入這個畫面， 來源 就會出現下拉式清單。選定格式後按一下 下一步 。
圖24-18 「選擇目的檔案格式」畫面 - 現在進入 儲存、排程和複製封裝 畫面，如圖 24-19 所示。這裡可以設定是否儲存 DTS 封裝以便稍後執行，設定方法和之前在 匯入精靈 所介紹的類似，只是這裡的選項會以反向方式執行匯出的動作。
圖24-19 「儲存、排程和複製封裝」畫面 - 按一下 下一步 進入 正在完成 DTS 匯入／匯出精靈 畫面，如圖 24-20 所示。
圖24-20 「正在完成 DTS 匯入／匯出精靈」畫面 - 當您按一下 完成 按鈕時，匯出程序開始執行，出現 正在執行封裝 畫面，如圖 24-21 所示，程序完成後會出現匯出成功或失敗的訊息。
匯入精靈 和 匯出精靈 的使用簡化了許多設定上繁瑣的步驟，但請您記得如果需要重複執行匯入或匯出的動作，花些時間寫指令碼會節省重複執行的時間。您可以建立使用 BULK INSERT 陳述式執行匯入或使用 SELECT 陳述式指定匯出的指令碼。
說明
在範例中我們皆使用文字檔與資料庫間的轉換，事實上精靈可以執行更多資料型別的轉換，特別對於資料庫或其他資料來源之間轉換資料，精靈尤其有用。
圖24-21 「正在執行封裝」畫面 Staging 資料表
Staging 資料表是一個暫存資料表，您可以用來建立、處理和操作將會載入 SQL Server 的資料，並且將這些資料複製到資料庫內部的適當資料表。本節會學習如何使用 Staging 資料表。
Staging 資料表基礎
Staging 資料表是一個在資料庫中的暫存檔案儲存區，您可以將資料複製進Staging 資料表。然後您可以利用 T-SQL 執行如關聯性的操作、將資料轉換成想要的格式等等。這樣的轉換會需要用到 Staging 資料表和其他已存在的資料表。
使用 Staging 資料表處理載入的資料，可以免除某些資料載入方法對載入的限制。大部分的資料載入技術只能讓資料複製到資料庫，而沒有加以處理。使用 DTS，可以執行一些資料轉換，但是無法轉換在資料庫中的現有資料。使用 Staging 資料表的主要優點就是您可以基於在 Staging 資料表本身的資訊或現存資料表中的資訊，執行聯結操作。
使用 Staging 資料表
本節中會用三個範例示範使用 Staging 資料表，分別是合併和載入資料表、載入及分割資料表、載入唯一值到一個資料表，這三個範例可以示範 Staging 資料表如何輔助資料的載入。
合併和載入資料表
想像一個在資料超市（Data Mart）中的資料表，該資料表由兩個由線上交易（OLTP）系統中的資料表聯合組成，該資料表有 A、B、C、D、E 五個資料行。資料行 A、B、C 在同一資料表內，而資料行 C、D、E 則在另一資料表內。這兩個輸入的資料表都可透過暫存方式，並且使用一個聯結操作載入資料超市中，圖 24-22 為操作圖示。
圖24-22 利用 Staging 資料表執行聯結操作 載入和分割資料表
在第一個範例中，如果依照正常的情況，應該會將資料表載入資料超市中的數個資料表內（為了正規化的目的）。要能輕易完成這個工作，請先將資料複製到 Staging 資料表中，再使用兩個查詢將 Staging 資料表中的資料載入資料超市中的資料表，如圖 24-23 所示。
圖24-23 使用 Staging 資料表分割資料 載入唯一值到一個資料表
要確保載入資料的唯一性，您可以利用大量複製的方法將資料複製進 Staging 資料表中，然後使用 T-SQL 陳述式將主要資料表中沒有的值，從 Staging 資料表插入主要資料表中。當載入的資料可能違反商業規則時，這個選項會很有用。使用以下的陳述式，可以將唯一值從 Staging 資料表中複製到主要資料表：
INSERT INTO table (columnA, columnB) SELECT columnA, columnB FROM staging_table WHERE columnA NOT IN (SELECT columnA FROM table)陳述式看起來複雜，其實只不過是確認主要資料表內有沒有和資料行 A 相同的資料，如果沒有，則將 Staging 資料表中的兩個資料行（A 和 B）載入主資料表中，以確保插入資料值的唯一性。
SELECT...INTO 陳述式
使用 SELECT...INTO 陳述式並不算一個正式用來載入資料的方法，比較正確的說法應該說是從已經存在的資料表或 Staging 資料表中取得資料，然後建立一個新資料表，再載入資料的方法。
說明
由於 SELECT...INTO 陳述式工作有一定限制，資料庫選項select into/bulkcopy必須設為 TRUE。要設定該選項，請使用下面的 T-SQL 陳述式：
exec sp_dboption <database_name>, "select into/bulkcopy", TRUE
SELECT...INTO 陳述式的語法如下：
SELECT <column_list> INTO <new_table_name> <select_clause>
select_clause變數指的是可以和 SELECT 陳述式聯合使用的陳述式或其他選項，例如 FROM 和 WHERE。SELECT...INTO 陳述式簡單易用，如下面的例子。
exec sp_dboption "example", "select into/bulkcopy", TRUE GO SELECT order_id, contact_id, item_id, item_description, amount INTO newsales FROM stage GO exec sp_dboption "example", "select into/bulkcopy", FALSE GO在這裡的資料庫的名稱為「example」，建立一個資料表名稱為newsales，取出資料的資料表是stage。
本章總結
在本章中，我們學到了 BCP、BULK INSERT 陳述式和 DTS 的使用方式，也介紹了 Staging 資料表和 SELECT...INTO 陳述式，這些工具和技術提供很多益處，因為載入資料至資料庫正是 DBA 的主要任務之一。 第二十五章 將開始學習分散式交易協調員（MicrosoftDistributed Transaction Coordinator，MSDTC）以及 Microsoft Transaction Server（MTS）。
- ORDER（column[ASC | DESC]） 指定資料行中的資料排序。
1148
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
