格式化匯入和匯出資料的檔案 (SQL Server)

  • 發行項

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

當您將資料大量匯入 SQL Server 資料表，或從資料表大量匯出資料時，可以使用格式檔案來儲存大量匯出或大量匯入資料所需的所有格式資訊。 這包含相對於該資料表之資料檔案中各欄位的格式資訊。

SQL Server 支援兩種類型的格式檔案：XML 格式檔案和非 XML 格式檔案。 非 XML 格式檔案和 XML 格式檔案都會將每個欄位的描述包含在資料檔中，而且 XML 格式檔案也包含對應資料表資料行的描述。 一般而言，XML 和非 XML 格式檔案可以互換使用， 但是，仍建議您在新的格式檔案中使用 XML 語法，因為 XML 比非 XML 格式檔案多了一些優點。 如需詳細資訊，請參閱 XML 格式檔案 (SQL Server)。

注意

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

格式檔案的優點

格式檔案提供可用來寫入資料檔的彈性系統，幾乎不需要進行編輯，即可符合其他資料格式或從其他軟體中讀取資料檔案。

您可以大量匯入資料，而不需要新增或刪除不需要的資料，或是重新排列資料檔案中的現有資料。 當資料檔案欄位與資料表資料行不相符時，格式檔案就會很有用。

格式檔案的範例

下列範例顯示非 XML 格式檔案及 XML 格式檔案的配置。 這些格式檔案會對應到 HumanResources.myTeam 範例資料庫中的 AdventureWorks2022 資料表。 這個資料表包含四個資料行： EmployeeID、 Name、 Title和 ModifiedDate。

注意

如需此資料表及建立方式的相關資訊，請參閱 HumanResources.myTeam 範例資料表 (SQL Server)。

A. 使用非 XML 格式檔案

下列非 XML 格式檔案使用 HumanResources.myTeam 資料表的 SQL Server 原生資料格式。 這個格式檔案是使用下列 bcp 命令所建立。

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T

bcp 命令預設為使用 Windows 驗證 SQL Server 的本機預設執行個體。 您可以視需要指定其他執行個體和登入資訊，如需詳細資訊，請參閱 bcp 公用程式。 例如，若要使用 Windows 驗證指定遠端伺服器具名執行個體，請使用：

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T -S servername/instancename

此格式檔案的內容如下所示，從 SQL Server 的主要版本號碼開始，然後是資料表中繼資料資訊。

14.0
4
1       SQLSMALLINT   0       2       ""   1     EmployeeID               ""
2       SQLNCHAR      2       100     ""   2     Name                     SQL_Latin1_General_CP1_CI_AS
3       SQLNCHAR      2       100     ""   3     Title                    SQL_Latin1_General_CP1_CI_AS
4       SQLNCHAR      2       100     ""   4     Background               SQL_Latin1_General_CP1_CI_AS

如需詳細資訊，請參閱使用非 XML 格式檔案 (SQL Server)。

B. 使用 XML 格式檔案

下列 XML 格式檔案使用 HumanResources.myTeam 資料表的 SQL Server 原生資料格式。 這個格式檔案是使用下列 bcp 命令所建立。

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

格式檔案包含：

<?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="NativePrefix" LENGTH="1"/>
  <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="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="EmployeeID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Background" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

如需詳細資訊，請參閱 XML 格式檔案 (SQL Server)。

何時需要格式檔案？

在下列情況下，通常需要格式檔案：

  • 使用 INSERT ... SELECT * FROM OPENROWSET(BULK...) 陳述式時。

  • 使用 bcp 或 BULK INSERT 的複雜大量匯入情況。

  • 多個具有不同結構描述的資料表，使用同一個資料檔案做為來源。

  • 資料檔的欄位數目和目標資料表的資料行數目不同；例如：

    • 目標資料表至少有一個資料行，其中定義了預設值或允許 NULL。
    • 使用者在資料表中的一個或多個資料行上沒有 SELECT/INSERT 權限。
    • 兩個以上具有不同結構描述的資料表，使用單一資料檔。

  • 資料檔案與資料表的資料行順序不同。

  • 資料檔案的資料行之間，有不同的終止字元或前置長度。

注意

在沒有格式檔案的情況下，如果 bcp 命令指定資料格式參數 (-n、-c、-w 或 -N)，或 BULK INSERT 作業指定 DATAFILETYPE 選項，則會採用指定的資料格式來做為解譯資料檔欄位的預設方法。

相關內容