建立格式檔案 (SQL Server)

適用範圍: 是SQL Server (所有支援的版本) 是Azure SQL Database 是Azure SQL 受控執行個體 是Azure Synapse Analytics 是平行處理資料倉儲

當您將資料大量匯入 SQL Server 資料表,或從資料表大量匯出資料時,可以使用格式檔案提供可用來寫入資料檔的彈性系統,幾乎不需要進行編輯即可符合其他資料格式,或是從其他軟體程式讀取資料檔。

SQL Server 支援兩種類型的格式檔案:非 XML 格式和 XML 格式。 非 XML 格式是舊版 SQL Server所支援的原始格式。

一般而言,XML 和非 XML 格式檔案可以互換使用, 但是,仍建議您在新的格式檔案中使用 XML 語法,因為 XML 比非 XML 格式檔案多了一些優點。

注意

用於讀取格式檔案的 bcp 公用程式 (Bcp.exe) 版本,必須與用於建立格式檔案的版本相同或比它更新。 例如,SQL Server 2012 (11.x) bcp 可以讀取由 SQL Server 2008bcp 產生的 10.0 版格式檔案,但 SQL Server 2008 bcp 無法讀取由 SQL Server 2012 (11.x) bcp 產生的 11.0 版格式檔案。

此主題描述如何使用 bcp 公用程式 來建立特定資料表的格式檔案。 格式檔案以指定的資料類型選項 ( -n-c-w,或 -N) 與資料表或檢視分隔符號為基礎。

建立非 XML 格式檔案

使用 bcp 命令建立格式檔案時,請指定 format 引數並使用 nul 取代資料檔案路徑。 format 選項也需要 -f 選項,例如:

bcp table_or_view format nul -fformat_file_name

注意

為了區分非 XML 格式檔案,建議您使用 .fmt 做為副檔名,例如 MyTable.fmt。

如需有關非 XML 格式檔案之結構與欄位的詳細資訊,請參閱 非 XML 格式檔案 (SQL Server)所支援的原始格式。

範例

本節包含下列範例,說明如何使用 bcp 命令建立非 XML 格式檔案:

  • A. 建立原生資料的非 XML 格式檔案

  • B. 建立字元資料的非 XML 格式檔案

  • C. 建立 Unicode 原生資料的非 XML 格式檔案

  • D. 建立 Unicode 字元資料的非 XML 格式檔案

  • F. 使用格式檔案與字碼頁選項

這些範例使用 HumanResources.Department 範例資料庫中的 AdventureWorks2012 資料表。 HumanResources.Department 資料表包含四個資料行: DepartmentIDNameGroupNameModifiedDate

A. 建立原生資料的非 XML 格式檔案

下列範例會建立 Department-n.xml資料表的 XML 格式檔案 AdventureWorks2012HumanResources.Department 。 格式檔案使用原生資料類型。 所產生之格式檔案的內容,會出現在命令之後。

bcp 命令包含下列限定詞。

限定詞 描述
formatnul-f format_file 指定非 XML 格式檔案。
-n 指定原生資料類型。
-T 指定 bcp 公用程式使用整合式安全性的信任連接,連接至 SQL Server 。 如果未指定 -T ,則必須指定 -U-P ,才能順利登入。

在 Windows 命令提示字元中,輸入下列 bcp 命令:

bcp AdventureWorks2012.HumanResources.Department format nul -T -n -f Department-n.fmt  

產生的格式檔案 Department-n.fmt包含下列資訊:

12.0  
4  
1  SQLSMALLINT   0       2       ""   1     DepartmentID         ""  
2  SQLNCHAR      2       100     ""   2     Name                 SQL_Latin1_General_CP1_CI_AS  
3  SQLNCHAR      2       100     ""   3     GroupName            SQL_Latin1_General_CP1_CI_AS  
4  SQLDATETIME   0       8       ""   4     ModifiedDate         ""  

如需詳細資訊,請參閱 非 XML 格式檔案 (SQL Server)所支援的原始格式。

B. 建立字元資料的非 XML 格式檔案

下列範例會建立 Department.fmt資料表的 XML 格式檔案 AdventureWorks2012HumanResources.Department 。 格式檔案使用字元資料格式和非預設欄位結束字元 (,)。 所產生之格式檔案的內容,會出現在命令之後。

bcp 命令包含下列限定詞。

限定詞 描述
formatnul-f format_file 指定非 XML 格式檔案。
-c 指定字元資料。
-T 指定 bcp 公用程式使用整合式安全性的信任連接,連接至 SQL Server 。 如果未指定 -T ,則必須指定 -U-P ,才能順利登入。

在 Windows 命令提示字元中,輸入下列 bcp 命令:

bcp AdventureWorks2012.HumanResources.Department format nul -c -f Department-c.fmt -T  

產生的格式檔案 Department-c.fmt包含下列資訊:

12.0  
4  
1  SQLCHAR       0       7       "\t"     1     DepartmentID            ""  
2  SQLCHAR       0       100     "\t"     2     Name                    SQL_Latin1_General_CP1_CI_AS  
3  SQLCHAR       0       100     "\t"     3     GroupName               SQL_Latin1_General_CP1_CI_AS  
4  SQLCHAR       0       24      "\r\n"   4     ModifiedDate            ""  

如需詳細資訊,請參閱 非 XML 格式檔案 (SQL Server)所支援的原始格式。

C. 建立 Unicode 原生資料的非 XML 格式檔案

若要針對 HumanResources.Department 資料表的 Unicode 原生資料建立非 XML 格式檔案,請使用下列命令:

bcp AdventureWorks2012.HumanResources.Department format nul -T -N -f Department-n.fmt  

如需如何使用 Unicode 原生資料的詳細資訊,請參閱使用 Unicode 原生格式匯入或匯出資料 (SQL Server)

D. 建立 Unicode 字元資料的非 XML 格式檔案

若要針對使用預設結束字元之 HumanResources.Department 資料表的 Unicode 字元資料建立非 XML 格式檔案,請使用下列命令:

bcp AdventureWorks2012.HumanResources.Department format nul -T -w -f Department-w.fmt  

如需如何使用 Unicode 字元資料的詳細資訊,請參閱使用 Unicode 字元格式匯入或匯出資料 (SQL Server)

F. 使用格式檔案與字碼頁選項

若使用 bcp 命令 (也就是 bcp format) 建立格式檔案,就會在格式檔案中寫入定序/字碼頁的資訊。
下列範例格式檔案屬於具 5 個資料行的資料表且包含定序。

13.0  
5  
1  SQLCHAR         0       0       "**\t**"         1     c_0          Cyrillic_General_CS_AS  
2  SQLCHAR         0       0       "**\t**"         2     c_1          Cyrillic_General_CS_AS  
3  SQLCHAR         0       3000    "**\t**"         3     c_2          Cyrillic_General_CS_AS  
4  SQLCHAR         0       5       "**\t**"         4     c_3          ""  
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4          ""  
  

如果您嘗試使用 bcp in -c -C65001 -f format_file ..." 或 "BULK INSERT/OPENROWSET ... FORMATFILE='format_file' CODEPAGE=65001 ...",將資料匯入至 SQL Server,則定序/字碼頁的相關資訊將會優先於 65001 選項。
因此,如果您產生格式檔案,則必須從產生的格式檔案手動刪除定序資訊,然後才開始將資料匯回 SQL Server。
下列範例為不具定序資訊的格式檔案。

13.0  
5  
1  SQLCHAR         0       0       "**\t**"         1     c_0              ""  
2  SQLCHAR         0       0       "**\t**"         2     c_1              ""  
3  SQLCHAR         0       3000    "**\t**"         3     c_2              ""  
4  SQLCHAR         0       5       "**\t**"         4     c_3              ""  
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4              ""  

建立 XML 格式檔案

使用 bcp 命令建立格式檔案時,請指定 format 引數並使用 nul 取代資料檔案路徑。 format 選項一律需要 -f 選項,您也必須指定 -x 選項才能建立 XML 格式檔案,例如:

bcp table_or_view format nul-f format_file_name -x

注意

為了區分 XML 格式檔案,建議您使用 .xml 做為副檔名,例如 MyTable.xml。

如需有關 XML 格式檔案之結構與欄位的詳細資訊,請參閱 XML 格式檔案 (SQL Server)所支援的原始格式。

範例

本節包含下列範例,說明如何使用 bcp 命令建立 XML 格式檔案:

  • A. 建立字元資料的 XML 格式檔案
  • B. 建立原生資料的 XML 格式檔案

這些範例使用 HumanResources.Department 範例資料庫中的 AdventureWorks2012 資料表。 HumanResources.Department 資料表包含四個資料行: DepartmentIDNameGroupNameModifiedDate

注意

Adventure Works Cycles 是虛構的製造公司,用於示範資料庫概念與案例。

A. 建立字元資料的 XML 格式檔案

下列範例會建立 Department.xml資料表的 XML 格式檔案 AdventureWorks2012HumanResources.Department 。 格式檔案使用字元資料格式和非預設欄位結束字元 (,)。 所產生之格式檔案的內容,會出現在命令之後。

bcp 命令包含下列限定詞。

限定詞 描述
formatnul-f format_file -x 指定 XML 格式檔案。
-c 指定字元資料。
-t , 指定逗號 ( , ) 作為欄位結束字元。

注意:如果資料檔使用預設欄位結束字元 (\t),則不需要 -t 參數。
-T 指定 bcp 公用程式使用整合式安全性的信任連接,連接至 SQL Server 。 如果未指定 -T ,則必須指定 -U-P ,才能順利登入。

在 Windows 命令提示字元中,輸入下列 bcp 命令:

bcp AdventureWorks2012.HumanResources.Department format nul -c -x -f Department-c.xml -t, -T  

產生的格式檔案 Department-c.xml包含下列 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="24"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>  
 </ROW>  
</BCPFORMAT>  

如需本格式檔案語法的相關資訊,請參閱 XML 格式檔案 (SQL Server)。 如需字元資料的相關資訊,請參閱使用字元格式匯入或匯出資料 (SQL Server)

B. 建立原生資料的 XML 格式檔案

下列範例會建立 Department-n.xml資料表的 XML 格式檔案 HumanResources.Department 。 格式檔案使用原生資料類型。 所產生之格式檔案的內容,會出現在命令之後。

bcp 命令包含下列限定詞。

限定詞 描述
formatnul-f format_file -x 指定 XML 格式檔案。
-n 指定原生資料類型。
-T 指定 bcp 公用程式使用整合式安全性的信任連接,連接至 SQL Server 。 如果未指定 -T ,則必須指定 -U-P ,才能順利登入。

在 Windows 命令提示字元中,輸入下列 bcp 命令:

bcp AdventureWorks2012.HumanResources.Department format nul -x -f Department-n.xml -n -T  

產生的格式檔案 Department-n.xml包含下列 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="NativeFixed" LENGTH="2"/>  
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="4" xsi:type="NativeFixed" LENGTH="8"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>  
 </ROW>  
</BCPFORMAT>  

如需本格式檔案語法的相關資訊,請參閱 XML 格式檔案 (SQL Server)。 如需如何使用原生資料的相關資訊,請參閱使用原生格式匯入或匯出資料 (SQL Server)

將資料欄位對應至資料表資料行

格式檔案由 bcp 建立後,便會依序描述所有的資料表資料行。 您可以修改格式檔案,重新排列或省略資料表資料列。 這可讓您針對欄位未直接對應至資料表資料行的資料檔來自訂格式檔案。 如需詳細資訊,請參閱下列主題:

另請參閱

bcp 公用程式
使用格式檔案將資料表資料行對應至資料檔案欄位 (SQL Server)
使用格式檔案略過資料表資料行 (SQL Server)
使用格式檔案略過資料欄位 (SQL Server)
非 XML 格式檔案 (SQL Server)
XML 格式檔案 (SQL Server)