共用方式為


使用格式檔案以略過資料表資料行

更新: 2006 年 7 月 17 日

本主題將說明格式檔案。當欄位不存在於資料檔案中時,您就可以使用格式檔案來略過資料表資料行的匯入。只有在略過的資料行可為 Null 和/或有預設值時,資料檔案所包含的欄位才可以少於資料表中的資料行數目。

範例資料表與資料檔

下列範例會要求在 dbo 結構描述下之 AdventureWorks 範例資料庫中有一個名為 myTestSkipCol 的資料表。使用如下陳述式建立此資料表:

USE AdventureWorks;
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 AdventureWorks..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

上述命令會建立非 XML 格式檔案 myTestSkipCol_Default.fmt。這個格式檔案就稱為*「預設格式檔案」*,因為它是由 bcp 產生的格式。一般而言,預設格式檔案描述資料檔欄位與資料表資料行之間的一對一對應。

ms179250.security(zh-tw,SQL.90).gif安全性注意事項:
您可能需要指定您要連接的伺服器執行個體的名稱。此外,也可能需要指定使用者名稱和密碼。如需詳細資訊,請參閱<bcp 公用程式>。

下列圖解顯示了這個範例預設格式檔案中的值。此圖解同時顯示每個格式檔欄位的名稱。

myTestSkipCol 的預設非 XML 格式檔案

ms179250.note(zh-tw,SQL.90).gif附註:
如需有關格式檔欄位的詳細資訊,請參閱<瞭解非 XML 格式檔案>。

修改非 XML 格式檔案的方法

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

  • 慣用的方法包括三個基本步驟。首先,刪除資料檔案中遺失的、任何描述欄位的格式檔資料列。接著,減少所刪除的資料列之後的、每個格式檔資料列的「主檔案欄位順序」值。目的是要產生連續的「主檔案欄位順序」值 (1 到 <n>),以反映每個資料欄位在資料檔案中的實際位置。最後,減少 [資料行數目] 欄位中的值,以反映資料檔案中欄位的實際數目。
    下列範例根據 myTestSkipCol 資料表的預設格式檔案,這是本主題先前在「建立預設的非 XML 格式檔案」中建立的格式檔。這個修改過的格式檔案將第一個資料欄位對應到 Col1、略過 Col2,然後對應第二個資料欄位到 Col3Col2 資料列現在已經刪除。其他修改的部分會以粗體顯示:

    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       0       0      ""     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 AdventureWorks;
GO
BULK INSERT myTestSkipCol 
   FROM 'C:\myTestSkipCol2.dat' 
   WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');
GO
SELECT * FROM myTestSkipCol;
GO

使用 XML 格式檔案

如果使用 XML 格式檔案,您就不能在直接匯入至資料表時,利用 bcp 命令或 BULK INSERT 陳述式來略過資料行。不過,您可以匯入資料表最後一個資料行以外的所有資料行。如果您必須略過最後一個資料行以外的任何資料行,就必須建立只有包含資料檔案中包含之資料行的目標資料表檢視。然後,您就可以從該檔案將大量資料匯入檢視。

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

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

建立預設的 XML 格式檔案

修改的格式檔案的範例以本主題先前在「範例資料表與資料檔」中建立的 myTestSkipCol 範例資料表與資料檔為基礎。下列 bcp 命令會建立 myTestSkipCol 資料表的預設 XML 格式檔案:

bcp AdventureWorks..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>
ms179250.note(zh-tw,SQL.90).gif附註:
如需有關 XML 格式檔案結構的詳細資訊,請參閱<了解 XML 格式檔案>。

範例

本節中的範例使用本主題先前在「範例資料表與資料檔」中建立的 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 AdventureWorks;
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 資料檔案匯入這個檢視。

在 SQL Server Management Studio 查詢編輯器中,執行下列程式碼:

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

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

請參閱

概念

使用格式檔案以略過資料欄位
使用格式檔案將資料表資料行對應到資料檔欄位
使用格式檔案大量匯入資料

其他資源

bcp 公用程式
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本 歷程記錄

2006 年 7 月 17 日

新增內容:
  • 新增用於建立範例格式檔案的 bcp 命令。
變更的內容:
  • 更新下列章節以配合 myTestSkipCol 範例資料表:
    • 建立預設的非 XML 格式檔案
    • 修改非 XML 格式檔案的方法
    • 建立預設的 XML 格式檔案