共用方式為


關於大量匯入和大量匯出作業

SQL Server 支援從 SQL Server 資料表大量匯出資料,以及將資料大量匯入 SQL Server 資料表或非資料分割的檢視。有下列基本方法可用。

方法

描述

匯入資料

匯出資料

bcp 公用程式

可大量匯出和大量匯入資料並產生格式檔案的命令列公用程式 (Bcp.exe)。

BULK INSERT 陳述式

Transact-SQL 陳述式,可將資料直接從資料檔案匯入至資料庫資料表或非資料分割的檢視。

INSERT ...SELECT * FROM OPENROWSET(BULK...)陳述式

Transact-SQL 陳述式,其指定 OPENROWSET(BULK…) 函數選取 INSERT 陳述式中的資料,以使用 OPENROWSET 大量資料列集提供者,將資料大量匯入 SQL Server 資料表。

限制

SQL Server 大量匯入作業不支援從逗號分隔值 (CSV) 檔案匯入資料。不過,在 32 位元系統上,您可以搭配 OLE DB Provider for Jet 使用 OPENROWSET,將 CSV 資料匯入 SQL Server 資料表,而不需要將大量匯入最佳化。Jet 會將文字檔視為資料表,其中包含與資料來源位於相同目錄中之 schema.ini 檔所定義的結構描述。若是 CSV 資料,schema.ini 檔中的其中一個參數將會是 "FORMAT=CSVDelimited"。若要使用此解決方案,您需要了解 Jet Test IISAMm 如何運作 (其連接字串語法、schema.ini 用法、登錄設定選項等等)。此資訊的最佳來源為 Microsoft Access 說明以及知識庫 (KB) 文件。如需詳細資訊,請參閱<初始化文字資料來源驅動程式>(英文)、<如何搭配安全保護 Access 資料庫的連結伺服器使用 SQL Server 7.0 分散式查詢>、<HOW TO:使用 Jet OLE DB Provider 4.0 連接到 ISAM 資料庫>(機器翻譯) 以及<如何使用 Jet 提供者的文字開啟分隔的文字檔案>(機器翻譯)。

同處理序與跨處理序的作業

BULK INSERT 陳述式 和 OPENROWSET(BULK) 函數與 SQL Server 執行於同處理序,共用相同的記憶體位址空間。因為資料檔是由 SQL Server 處理序所開啟,因此在用戶端處理序和 SQL Server 處理序之間並不會複製資料。如需有關使用 BULK INSERT 或 INSERT ...SELECT * FROM OPENROWSET(BULK...) 匯入資料時的安全性考量,請參閱<使用 BULK INSERT 或 OPENROWSET(BULK...) 匯入大量資料>。

相反地,bcp 公用程式會跨處理序執行。若要在處理序記憶體空間之間移動資料,bcp 必須使用「處理序間資料封送處理」。「處理序間資料封送處理」是將方法呼叫的參數轉換成位元組資料流的程序。這會大量增加處理器的負擔。不過,因為 bcp 會剖析資料並在用戶端處理序中將資料轉換成原生儲存格式,所以可以從 SQL Server 處理序中卸載剖析和資料轉換。因此,如果您有 CPU 限制,可以在有不只一個 CPU 的電腦上,或在不同電腦上使用 bcp 來獲得較佳的大量匯入效能,而不要使用 BULK INSERT 或 INSERT ...SELECT * FROM OPENROWSET(BULK)。

格式檔案

bcp 公用程式、BULK INSERT 和 INSERT ...SELECT * FROM OPENROWSET(BULK...)全都支援使用特殊的「格式檔案」(Format File),以儲存資料檔案中每一個欄位的格式資訊。格式檔案也可以包含對應的 SQL Server 資料表的相關資訊。對 SQL Server 執行個體大量匯出與大量匯入資料時,格式檔案可以提供所需的所有格式資訊。

格式檔案提供彈性方式,在匯入期間用於解譯資料檔中的資料,以及在匯出期間用於格式化資料檔中的資料。這樣的彈性讓您不需撰寫特殊用途的程式碼來解譯資料,也不需因應 SQL Server 或外部應用程式的特定需求將資料重新格式化。例如,如果您大量匯出的資料即將要載入到需要逗號分隔值的應用程式中,則可以使用格式檔案,在匯出的資料中插入逗號當做欄位結束字元。

SQL Server 2005 及更新的版本支援下列兩種類型的格式檔案:XML 格式檔案和非 XML 格式檔案。舊版的 SQL Server 支援非 XML 格式檔案;XML 格式檔案則是 SQL Server 2005 的新功能。

bcp 公用程式是唯一可以產生格式檔案的工具。如需詳細資訊,請參閱<建立格式檔案>。如需有關格式檔案的詳細資訊,請參閱<用於匯入或匯出資料的格式檔案>。

[!附註]

萬一在大量匯出或匯入作業期間未提供格式檔案,使用者可以選擇在命令列覆寫預設格式。

查詢處理器和大量匯入

為了將資料大量匯入 SQL Server 的執行個體,bcp 公用程式、BULK INSERT 陳述式和 INSERT ...SELECT * FROM OPENROWSET(BULK...)陳述式都會搭配查詢處理器使用。

這三個方法都會將資料檔案中的資料轉換成 OLE DB 資料列集。但是轉換方法各有不同,如下所述:

  • bcp 公用程式讀取資料檔案,並將 TDS 資料流傳送至 SQL Server 大量複製程式 (BCP) API,此 API 便會將資料轉換為 OLE DB 資料列集。

  • BULK INSERT 和 OPENROWSET 大量資料列集提供者都會將檔案資料直接轉換為 OLE DB 資料列集。

查詢處理器會將 OLE DB 資料列集插入目標資料表中,並自行規劃及最佳化每一項作業。

效能考量

當有大量資料要匯入時,效能考量因素也就顯得相當重要。在某些情況下,變更大量匯入或大量匯出作業處理下列其中一項或多項的方式,將可以改進效能:

  • 批次參數

  • CHECK 條件約束的條件約束檢查

  • 記錄大量交易的方式。這與通常使用完整復原模式的資料庫有關。

  • 排序匯出的資料

  • 平行資料匯入

  • 資料表鎖定

  • 觸發程序執行

如需詳細資訊,請參閱<最佳化大量匯入效能>。

[!附註]

大量匯出作業並沒有什麼特別的最佳化技術。這些作業不過是使用 SELECT 陳述式,從來源資料表中選取資料而已。