使用格式檔案大量匯入資料 (SQL Server)

發行項
06/13/2017

本主題說明在大容量導入作業中使用格式檔案。格式檔案會將數據檔的欄位對應至數據表的數據行。當使用 bcp 命令或 BULK INSERT 或 INSERT 時，您可以使用非 XML 或 XML 格式檔案大容量導入數據。SELECT * FROM OPENROWSET（BULK...）Transact-SQL 命令。

重要

若要讓格式檔案使用 Unicode 字元資料檔，所有輸入字段都必須是 Unicode 文字字串（也就是固定大小或字元終止的 Unicode 字串）。

注意

如果您不熟悉格式檔案，請參閱非 XML 格式檔案（SQL Server）和 XML 格式檔案（SQL Server）。

大容量導入命令的格式檔案選項

下表摘要說明每個大容量導入命令的 format-file 選項。

大量載入命令	使用 Format-File 選項
BULK INSERT	FORMATFILE = 'format_file_path'
INSERT ...SELECT * FROM OPENROWSET(BULK...)	FORMATFILE = 'format_file_path'
bcp ... 在	-f format_file

如需詳細資訊，請參閱 bcp 公用程式、BULK INSERT（Transact-SQL）或 OPENROWSET （Transact-SQL）。

注意

若要大量匯出或匯入 SQLXML 資料，請在格式檔案中使用下列其中一種資料類型：SQLCHAR 或 SQLVARYCHAR (資料會以用戶端字碼頁或定序所隱含的字碼頁傳送)、SQLNCHAR 或 SQLNVARCHAR (資料會以 Unicode 傳送)，或是 SQLBINARY 或 SQLVARYBIN (資料不經轉換即傳送)。

範例

本節中的範例說明如何使用 bcp 命令和 BULK INSERT 和 INSERT ...，使用格式檔案大容量導入數據。SELECT * FROM OPENROWSET（BULK...）語句。您必須先建立範例數據表、數據檔和格式檔案，才能執行其中一個大容量導入範例範例。

範例資料表

這些範例會要求在 dbo 架構下的 AdventureWorks2012 範例資料庫中建立名為 myTestFormatFiles 數據表的數據表。 若要建立此數據表，請在 SQL Server Management Studio 查詢編輯器中執行：

USE AdventureWorks2012;  
GO  
CREATE TABLE myTestFormatFiles (  
   Col1 smallint,  
   Col2 nvarchar(50),  
   Col3 nvarchar(50),  
   Col4 nvarchar(50)  
   );  
GO

範例資料檔案

這些範例會使用包含下列記錄的範例數據檔 myTestFormatFiles-c.Dat。若要建立數據檔，請在Microsoft Windows 命令提示字元中，輸入：

10,Field2,Field3,Field4  
15,Field2,Field3,Field4  
46,Field2,Field3,Field4  
58,Field2,Field3,Field4

範例格式檔案

本節中的部分範例使用 XML 格式檔案， myTestFormatFiles-f-x-c.Xml而其他範例則使用非 XML 格式檔案。這兩種格式檔案都使用字元數據格式和非預設欄位終止符（，）。

範例非 XML 格式檔案

下列範例會使用 bcp 從 myTestFormatFiles 數據表產生 XML 格式檔案。檔案 myTestFormatFiles.Fmt 包含下列資訊：

9.0  
4  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       100     ","      2     Col2         SQL_Latin1_General_CP1_CI_AS  
3       SQLCHAR       0       100     ","      3     Col3         SQL_Latin1_General_CP1_CI_AS  
4       SQLCHAR       0       100     "\r\n"   4     Col4         SQL_Latin1_General_CP1_CI_AS

若要使用 bcp 搭配格式選項來建立此格式檔案，請在 Windows 命令提示字元中輸入：

bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

如需建立格式檔案的詳細資訊，請參閱建立格式檔案（SQL Server）。

範例 XML 格式檔案

下列範例會使用 bcp 建立來從 myTestFormatFiles 數據表產生 XML 格式檔案。檔案 myTestFormatFiles.Xml 包含下列資訊：

<?xml version="1.0"?>  
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
 <RECORD>  
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>  
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>  
 </ROW>  
</BCPFORMAT>

若要使用 bcp 搭配格式選項來建立此格式檔案，請在 Windows 命令提示字元中輸入：

bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

使用 bcp

下列範例會使用 bcp 將數據從 myTestFormatFiles-c.Dat 數據檔 HumanResources.myTestFormatFiles 大容量導入範例資料庫中的數據表。這個範例使用 XML 格式檔案 MyTestFormatFiles.Xml。此範例會在匯入資料檔之前刪除任何現有的數據表數據列。

在 Windows 命令提示字元中，輸入：

bcp AdventureWorks2012..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T

注意

如需此命令的詳細資訊，請參閱 bcp 公用程式。

使用 BULK INSERT

下列範例使用 BULK INSERT 將數據從 myTestFormatFiles-c.Dat 數據檔 HumanResources.myTestFormatFiles 大容量導入 AdventureWorks2012 範例資料庫中的數據表。這個範例使用非 XML 格式檔案 MyTestFormatFiles.Fmt。此範例會在匯入資料檔之前刪除任何現有的數據表數據列。

在 SQL Server Management Studio 查詢編輯器中，執行：

USE AdventureWorks2012;  
GO  
DELETE myTestFormatFiles;  
GO  
BULK INSERT myTestFormatFiles   
   FROM 'C:\myTestFormatFiles-c.Dat'   
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');  
GO  
SELECT * FROM myTestFormatFiles;  
GO

注意

如需此語句的詳細資訊，請參閱 BULK INSERT （Transact-SQL）。

使用 OPENROWSET 大量數據列集提供者

下列範例會使用 INSERT ... SELECT * FROM OPENROWSET(BULK...) 將數據文件HumanResources.myTestFormatFiles中的數據myTestFormatFiles-c.Dat大容量導入範例資料庫中的AdventureWorks數據表。這個範例使用 XML 格式檔案 MyTestFormatFiles.Xml。此範例會在匯入資料檔之前刪除任何現有的數據表數據列。

在 SQL Server Management Studio 查詢編輯器中，執行：

USE AdventureWorks2012;  
DELETE myTestFormatFiles;  
GO  
INSERT INTO myTestFormatFiles  
    SELECT *  
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.Dat',  
      FORMATFILE='C:\myTestFormatFiles.Xml'       
      ) as t1 ;  
GO  
SELECT * FROM myTestFormatFiles;  
GO

當您完成使用範例數據表時，您可以使用下列語句卸除它：