使用格式檔案以略過資料表資料行 (SQL Server)

本主題描述格式檔案。當資料檔中沒有欄位時，您可以使用格式檔案來略過匯入資料表數據行。只有在略過的數據行可為 Null 且/或具有預設值時，數據檔才會包含的數據行數目少於數據表中的數據行數目。

範例數據表和數據檔

下列範例需要 dbo 架構下 AdventureWorks2012 範例資料庫中名為 myTestSkipCol 的數據表。使用如下陳述式建立此資料表：

USE AdventureWorks2012;
GO
CREATE TABLE myTestSkipCol 
   (
   Col1 smallint,
   Col2 nvarchar(50) NULL,
   Col3 nvarchar(50) not NULL
   );
GO

下列範例使用只包含兩個字段的範例數據檔， myTestSkipCol2.dat雖然對應的數據表包含三個數據行：

1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

若要將資料從 myTestSkipCol2.dat 批量匯入 myTestSkipCol 資料表，格式檔案必須將第一個資料欄位對應至 Col1，第二個欄位對應至 Col3，並略過 Col2。

使用非 XML 格式檔案

您可以修改非 XML 格式檔案，以略過資料表數據行。通常，這牽涉到使用 bcp 公用程式來建立預設的非 XML 格式檔案，以及在文本編輯器中修改預設檔案。修改的格式檔案必須將每個現有的欄位對應至其對應的數據表數據行，並指出要略過的數據表數據行。有兩個替代專案可用來修改預設的非 XML 資料檔。任一替代方案都表示數據欄位不存在於數據檔中，而且不會將數據插入對應的數據表數據行中。

建立預設的非 XML 格式檔案

本主題使用為範例數據表建立 myTestSkipCol 的預設非 XML 格式檔案，方法是使用下列 bcp 命令：

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

上述命令會建立非 XML 格式檔案 myTestSkipCol_Default.fmt。這個格式檔案稱為「預設格式檔案」 (Default Format File)，因為它是 bcp 所產生的格式。一般而言，預設格式檔案描述數據檔欄位與數據表數據行之間的一對一對應。

這很重要

您可能必須指定您要連線的伺服器實例名稱。此外，您可能必須指定使用者名稱和密碼。如需相關資訊，請參閱 bcp Utility。

下圖顯示此範例預設格式檔案中的值。此圖也會顯示每個格式檔案欄位的名稱。

myTestSkipCol 的預設非 XML 格式檔案

備註

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

修改非 XML 格式檔案的方法

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

慣用的方法牽涉到三個基本步驟。首先，刪除描述數據檔中遺漏欄位的任何格式檔案數據列。接著，減少所刪除的資料列之後的、每個格式檔資料列的「主檔案欄位順序」值。目的是要產生連續的「主檔案欄位順序」值 (1 到 n)，以反映每個資料欄位在資料檔案中的實際位置。最後，減少 [資料行數目] 欄位中的值，以反映資料檔案中欄位的實際數目。

下列範例是以本主題稍早在「建立預設非 XML 格式檔案」中建立之 myTestSkipCol 數據表的預設格式檔案為基礎。這個修改過的格式檔案將第一個資料欄位對應到 Col1、略過 Col2，然後對應第二個資料欄位到 Col3。 Col2 的資料列已被刪除。其他修改會以粗體表示：
```
9.0
2
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS
```
此外，若要略過資料表資料行，您也可以修改對應於資料表資料行的格式檔資料列的定義。在這個格式檔資料列中，[前置長度]、[主檔案資料長度] 和 [伺服器資料行順序] 值都必須設定為 0。此外，「終止符」和「數據行定序」字段必須設定為「“ （NULL）。

「伺服器數據行名稱」值需要非空白字串，但不需要實際的數據行名稱。其餘的格式欄位需要各自的預設值。

下列範例也是從 myTestSkipCol 資料表的預設格式檔案衍生的。必須是 0 或 NULL 的值會以粗體表示。
```
9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       00""0     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS
```

範例

下列範例也以 myTestSkipCol 本主題稍早在「範例數據表和數據檔」中建立的範例數據表和 myTestSkipCol2.dat 範例數據檔為基礎。

使用 BULK INSERT

此範例的運作方式是使用本主題稍早在「修改非 XML 格式檔案的方法」中建立的其中一個已修改的非 XML 格式檔案。在這個範例中，修改的格式檔案命名為 C:\myTestSkipCol2.fmt。若要使用 BULK INSERT 大容量導入 myTestSkipCol2.dat 數據檔，請在 SQL Server Management Studio 查詢編輯器中執行下列程式代碼：

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

使用 XML 格式檔案

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

若要使用 XML 格式檔案，使用 OPENROWSET（BULK...）略過資料表資料行，您必須在選取清單中以及目標數據表中提供明確的數據行清單，如下所示：

插入...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...)

建立預設 XML 格式檔案

修改格式檔案的範例是以本主題稍早在「範例數據表和數據檔」中建立的範例數據表和數據檔為基礎 myTestSkipCol 。下列 bcp 命令會建立 myTestSkipCol 資料表的預設 XML 格式檔案：

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

產生的預設非 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="\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)。

範例

本節中的範例會使用 myTestSkipCol 本主題稍早在「範例數據表和數據檔」中建立的範例數據表和 myTestSkipCol2.dat 範例數據檔。若要將資料從 myTestSkipCol2.dat 匯入資料表myTestSkipCol，範例會使用下列修改過的 XML 格式檔案 myTestSkipCol2-x.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="\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>

使用 OPENROWSET（BULK...）

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

在 SQL Server Management Studio 查詢編輯器中，執行下列程式代碼：

USE AdventureWorks2012;
GO
INSERT INTO myTestSkipCol
  (Col1,Col3)
    SELECT Col1,Col3
      FROM  OPENROWSET(BULK  'C:\myTestSkipCol2.Dat',
      FORMATFILE='C:\myTestSkipCol2.Xml'  
       ) as t1 ;
GO

在檢視上使用 BULK IMPORT

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