使用 bcp 建立格式檔案 (SQL Server)

  • 發行項

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

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

當您大量匯入 SQL Server 資料表或從資料表大量匯出資料時，您可使用格式檔案做為彈性系統來寫入資料檔案。 格式檔案幾乎或完全不需要進行編輯，即可符合其他資料格式或從其他軟體程式中讀取資料檔。

限制

用於讀取格式檔案的 bcp 公用程式 (bcp.exe) 版本，必須與用於建立格式檔案的版本相同或比它更新。 例如，SQL Server 2016 (13.x) bcp 可以讀取 SQL Server 2014 (12.x) bcp 產生的 12.0 版格式檔案，但 SQL Server 2014 (12.x) bcp 無法讀取 SQL Server 2016 (13.x) bcp 產生的 13.0 版格式檔案。

注意

Azure Synapse Analytics 中不支援此語法，包括大量插入。 在 Azure Synapse Analytics 和其他雲端資料庫平台整合中，透過 Azure Data Factory 中的 COPY 陳述式，或使用 COPY INTO 和 PolyBase 等 T-SQL 陳述式來完成資料移動。

建立格式檔案

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

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

本文需要 AdventureWorks2022 範例資料庫，您可以從 Microsoft SQL Server 範例和社群專案首頁) 下載。 Adventure Works Cycles 是虛構的製造公司，用於示範資料庫概念與案例。

使用 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 格式檔案。 HumanResources.Department 資料表包含四個資料行： DepartmentID、 Name、 GroupName和 ModifiedDate。

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

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

bcp 命令包含下列限定詞。

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

注意：如果資料檔案使用預設欄位結束字元 (\t)，則不需要 -t 參數。
-T 指定 bcp 公用程式使用整合式安全性的信任連線來連線至 SQL Server。 如果未指定 -T，則必須指定 -U 和 -P 以成功登入。

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

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

產生的格式檔案 Department-c.xml包含下列 XML 元素：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://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 命令包含下列限定詞。

限定詞 描述
format nul -x -f <format_file> 指定 XML 格式檔案。
-n 指定原生資料類型。
-T 指定 bcp 公用程式使用整合式安全性的信任連線來連線至 SQL Server。 如果未指定 -T，則必須指定 -U 和 -P 以成功登入。

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

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

產生的格式檔案 Department-n.xml包含下列 XML 元素：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://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建立後，便會依序描述所有的資料表資料行。 您可以修改格式檔案，重新排列或省略資料表資料列。 您可以針對欄位未直接對應至資料表資料行的資料檔案，來自訂格式檔案。 如需詳細資訊，請參閱下列文章：

相關內容