使用格式檔案略過資料表資料行（SQL Server）

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

本文說明如何使用格式檔案，在來源資料檔中不存在略過資料行的資料時，略過匯入資料表資料行。資料檔案可包含少於目的地資料表中資料行數量的欄位；也就是說，您可以跳過匯入資料行，但必須是目的資料表中，下列兩項條件中至少一項為 True 時：

跳過的資料行可以是空值。
跳過的資料行有預設值。

注意

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

範例資料表與資料檔案

本文中的範例預期 myTestSkipCol 結構描述下有一張名為 dbo 的資料表。您可以在範例資料庫 (例如 WideWorldImporters 或 AdventureWorks) 或任何其他資料庫中建立此資料表。使用如下陳述式建立此資料表：

USE WideWorldImporters;
GO

CREATE TABLE myTestSkipCol
(
    Col1 SMALLINT,
    Col2 NVARCHAR (50) NULL,
    Col3 NVARCHAR (50) NOT NULL
);
GO

本文中的範例也會使用範例資料檔案 myTestSkipCol2.dat。此資料檔案只包含兩個欄位，但目的地資料表包含三個資料行。

1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

基本步驟

您可以使用非 XML 格式檔案或 XML 格式檔案跳過資料表資料行。在這兩種情況下，都有兩個步驟：

使用 bcp 命令列公用程式來建立預設格式檔案。
修改文字編輯器中的預設格式檔案。

修改後的格式檔案必須將每個現有欄位對應到目的地資料表中相應的資料行。它還必須標示出要跳過資料表中的哪些欄位或欄位群組。

例如，若要將資料從 myTestSkipCol2.dat 大量匯入 myTestSkipCol 資料表，格式檔案必須將第一個資料欄位對應到 Col1、跳過 Col2，然後將第二個欄位對應到 Col3。

選項 1：使用非 XML 格式檔案

步驟 1 - 建立預設的非 XML 格式檔案

在命令提示字元中，執行下列 myTestSkipCol 命令以建立範例資料表的預設非 XML 格式檔案：

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

重要

您可能必須使用引數指定 -S 要連線的伺服器執行個體名稱。另外，也可能需要以 -U 和 -P 引數來指定使用者名稱和密碼。如需相關資訊，請參閱 bcp Utility。

上述命令會建立非 XML 格式檔案 myTestSkipCol_Default.fmt。此格式檔案稱為 預設格式檔案 ，因為它是由 bcp 產生的表單。預設格式檔案描述資料檔案欄位與資料表資料行之間的一對一對應。

下列螢幕擷取畫面顯示了這個範例預設格式檔案中的值。

詳細說明 mytestskipcol 預設非 XML 格式檔案的圖表。

注意

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

步驟 2 - 修改非 XML 格式檔案

若要修改預設的非 XML 格式檔案，有兩種替代方式。任一替代方案都表示資料欄位不存在於資料檔中，且不會將任何資料插入對應的資料表欄中。

若要略過資料表資料行，請編輯預設的非 XML 格式檔案，並且使用下列其中一個替代方法來修改檔案：

選項一 — 移除資料列

跳過資料欄的首選方法包含以下三個步驟：

首先，刪除描述來源資料檔案中遺失之欄位的任何格式檔案資料列。
接著，減少所刪除的資料列之後的、每個格式檔資料列的「主檔案欄位順序」值。目的是要產生連續的「主檔案欄位順序」值 (1 到 n)，以反映每個資料欄位在資料檔案中的實際位置。
最後，減少 [資料行數目] 欄位中的值，以反映資料檔案中欄位的實際數目。

下列範例以 myTestSkipCol 資料表的預設格式檔案為基礎。這個修改過的格式檔案將第一個資料欄位對應到 Col1、略過 Col2，然後對應第二個資料欄位到 Col3。 Col2 的資料列已被刪除。第一個欄位之後分隔的符號也已從 \t 變更為 ,。

14.0
2
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

選項 2 - 修改資料列定義

此外，若要略過資料表資料行，您也可以修改對應於資料表資料行的格式檔資料列的定義。在這個格式檔資料列中，[前置長度]、[主檔案資料長度] 和 [伺服器資料行順序] 值都必須設定為 0。此外，「終止符」和「直欄定序」欄位必須設定為 “” （亦即空白或 NULL 值）。「伺服器資料行名稱」值需要非空白字串，但實際的資料行名稱不是必需的。其餘的格式欄位需要各自的預設值。

下列範例也是從 myTestSkipCol 資料表的預設格式檔案衍生的。

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

非 XML 格式檔案的範例

下列範例以本文先前說明的 myTestSkipCol 範例資料表及 myTestSkipCol2.dat 範例資料檔案為基礎。

使用 BULK INSERT

使用如上一節所述來建立之任一個修改的非 XML 格式檔案，此範例就可運作。在這個範例中，修改的格式檔案命名為 myTestSkipCol2.fmt。若要使用 BULK INSERT 大量匯入 myTestSkipCol2.dat 資料檔案，請在 SQL Server Management Studio (SSMS) 中執行下列程式碼。更新您電腦上範例檔案位置的檔案系統路徑。

USE WideWorldImporters;
GO

BULK INSERT myTestSkipCol FROM 'C:\myTestSkipCol2.dat'
    WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');
GO

SELECT *
FROM myTestSkipCol;
GO

選項 2 - 使用 XML 格式檔案

步驟 1 - 建立預設的 XML 格式檔案

在命令提示字元中，執行下列 myTestSkipCol 命令以建立範例資料表的預設 XML 格式檔案：

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T

重要

上述命令會建立 XML 格式檔案 myTestSkipCol_Default.xml。此格式檔案稱為 預設格式檔案 ，因為它是由 bcp 產生的表單。預設格式檔案描述資料檔案欄位與資料表資料行之間的一對一對應。

<?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="\t" MAX_LENGTH="7" />
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
    <FIELD ID="3" 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" />
  </ROW>
</BCPFORMAT>

注意

如需 XML 格式檔案結構的相關資訊，請參閱 XML 格式檔案（SQL Server）。

步驟 2 - 修改 XML 格式檔案

以下是修改的 XML 格式檔案 myTestSkipCol2.xml，其跳過了 Col2。 FIELD 的 ROW 和 Col2 項目已移除，且項目已重新編號。第一個欄位之後分隔的符號也已從 \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="CharTerm" TERMINATOR="," MAX_LENGTH="7" />
    <FIELD ID="2" 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="Col3" xsi:type="SQLNVARCHAR" />
  </ROW>
</BCPFORMAT>

使用 XML 格式檔案的範例

下列範例以本文先前說明的 myTestSkipCol 範例資料表及 myTestSkipCol2.dat 範例資料檔案為基礎。

為了將資料從 myTestSkipCol2.dat 匯入 myTestSkipCol 資料表中，範例使用了修改過的 XML 格式檔案 myTestSkipCol2.xml。

使用 BULK INSERT 配合檢視

使用 XML 格式檔案時，當您使用 bcp 命令或 BULK INSERT 陳述式直接匯入資料表時，您無法略過資料行。不過，您可以匯入到資料表中除了最後一個欄位以外的所有欄位。如果您必須略過除最後一個資料行以外的任何資料行，就必須建立一個目標資料表檢視，其中只包含資料檔案中的資料行。然後，您就可以從該檔案將大量資料匯入檢視。

下列範例會在 v_myTestSkipCol 資料表上建立 myTestSkipCol 檢視。這個檢視會略過第二個資料表資料行 Col2。然後，此範例會使用 BULK INSERT ，將 myTestSkipCol2.dat 資料檔案匯入這個檢視。

請在 SSMS 中執行下列程式碼。更新您電腦上範例檔案位置的檔案系統路徑。

USE WideWorldImporters;
GO

CREATE VIEW v_myTestSkipCol AS
    SELECT Col1,
           Col3
    FROM myTestSkipCol;
GO

BULK INSERT v_myTestSkipCol FROM 'C:\myTestSkipCol2.dat'
    WITH (FORMATFILE = 'C:\myTestSkipCol2.xml');
GO

使用 OPENROWSET(BULK...)

若要透過使用 OPENROWSET(BULK...) 來使用 XML 格式檔案跳過資料表資料行，您必須在選取清單和目標資料表中提供明確的資料行清單，如下所示：

INSERT ...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...)

下列範例會使用 OPENROWSET 大量資料列集提供者和 myTestSkipCol2.xml 格式檔案。此範例會將 myTestSkipCol2.dat 資料檔案大量匯入 myTestSkipCol 資料表。此陳述式會依需要，在選取清單還有目標資料表中包含明確的資料行清單。