使用格式檔案跳過資料欄位 (SQL Server)

發行項
05/04/2023

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

資料檔所包含的欄位，可以比資料表中的資料行數多。此主題描述如何將資料表資料行對應到相對的資料欄位並忽略多餘欄位，藉以修改非 XML 格式檔案與 XML 格式檔案，讓資料檔能容納更多欄位。如需其他資訊，請參閱建立格式檔案 (SQL Server) 。

注意

您可以透過下列項目，使用非 XML 或 XML 格式檔案，將資料檔案大量匯入至資料表：bcp 公用程式命令、BULK INSERT 陳述式或 INSERT ...SELECT * FROM OPENROWSET(BULK...) 陳述式。如需詳細資訊，請參閱使用格式檔案大量匯入資料 (SQL Server)。

注意

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

範例測試條件

本主題中的修改格式檔案範例是以下面定義的資料表和資料檔案為基礎。

範例資料表

下列指令碼會建立測試資料庫和名為 myTestSkipField的資料表。在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL：

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

範例資料檔案

建立空白檔案 D:\BCP\myTestSkipField.bcp ，並插入下列資料：

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

建立格式檔案

若要從 myTestSkipField.bcp 大量匯入資料到 myTestSkipField 資料表中，格式檔案必須執行下列工作：

將第一個資料欄位對應到第一個資料行 PersonID。
略過第二個資料欄位。
將第三個資料欄位對應到第二個資料行 FirstName。
將第四個資料欄位對應到第三個資料行 LastName。

建立格式檔案的最簡單方法是使用 bcp 公用程式。首先，從現有的資料表建立基底格式檔案。其次，修改基底格式檔案以反映實際的資料檔案。

建立非 XML 格式檔案

如需詳細資訊，請檢閱非 XML 格式檔案 (SQL Server)。下列命令將使用 bcp 公用程式，根據 myTestSkipField.fmt的結構描述產生非 XML 格式檔案 myTestSkipField。另外還會使用限定詞 c 來指定字元資料、使用 t, 來指定逗號作為欄位結束字元，並使用 T 來指定使用整合式安全性的信任連接。請在命令提示字元之下，輸入下列命令：

bcp TestDatabase.dbo.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

修改非 XML 格式檔案

如需相關術語，請檢閱非 XML 格式檔案的結構。在 [記事本] 中開啟 D:\BCP\myTestSkipField.fmt 並執行下列修改：

複製 FirstName 的整個格式檔資料列，並將它直接貼到下一行的 FirstName 後面。
針對新資料列和所有後續資料列，將主機檔案欄位順序值加一。
增加資料行數目值，以反映資料檔案中的實際欄位數目。
針對第二個格式檔案資料列，將伺服器資料行順序從 2 修改為 0 。

比較進行的變更：

之前

13.0
3
1       SQLCHAR    0       7       ","      1     PersonID        ""
2       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       30      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

之後

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID     ""
2       SQLCHAR    0       25      ","      0     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       50      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

修改的格式檔案現在會反映：

4 個資料欄位
myTestSkipField.bcp 中的第一個資料欄位會對應到第一個資料行 myTestSkipField.. PersonID
myTestSkipField.bcp 中的第二個資料欄位未對應到任何資料行。
myTestSkipField.bcp 中的第三個資料欄位會對應到第二個資料行 myTestSkipField.. FirstName
myTestSkipField.bcp 中的第四個資料欄位會對應到第三個資料行、 myTestSkipField.. LastName

使用 XML 格式檔案

如需詳細資訊，請檢閱 XML 格式檔案 (SQL Server)。下列命令將使用 bcp 公用程式，根據 myTestSkipField.xml的結構描述建立 XML 格式檔案 myTestSkipField。另外還會使用限定詞 c 來指定字元資料、使用 t, 來指定逗號作為欄位結束字元，並使用 T 來指定使用整合式安全性的信任連接。必須使用 x 限定詞來產生 XML 格式檔案。請在命令提示字元之下，輸入下列命令：

bcp TestDatabase.dbo.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

修改 XML 格式檔案

如需相關術語，請檢閱 XML 格式檔案的結構描述語法。在 [記事本] 中開啟 D:\BCP\myTestSkipField.xml 並執行下列修改：

複製整個第二個欄位，並將它直接貼到下一行的第二個欄位後面。
針對新的 FIELD 以及每個後續 FIELD，將 "FIELD ID" 值加 1。
針對 FirstName和 LastName ，將 "COLUMN SOURCE" 值加 1，以反映修改過的對應。

比較進行的變更：

之前

<?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="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

之後

<?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="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

修改的格式檔案現在會反映：

4 個資料欄位
對應到 COLUMN 1 的 FIELD 1 會對應到第一個資料表資料行、 myTestSkipField.. PersonID
FIELD 2 未對應到任何 COLUMN，因此未對應至任何資料表資料行。
對應到 COLUMN 3 的 FIELD 3 會對應到第二個資料表資料行、 myTestSkipField.. FirstName
對應到 COLUMN 4 的 FIELD 4 會對應到第三個資料表資料行、 myTestSkipField.. LastName

使用格式檔案略過資料欄位來匯入資料

下列範例會使用上面建立的資料庫、資料檔案和格式檔案。

使用 bcp 和非 XML 格式檔案

請在命令提示字元之下，輸入下列命令：

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

使用 bcp 和 XML 格式檔案

請在命令提示字元之下，輸入下列命令：

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T

使用 BULK INSERT 和非 XML 格式檔案

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL：

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

使用 BULK INSERT 和 XML 格式檔案

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL：

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

使用 OPENROWSET(BULK...) 和非 XML 格式檔案

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL：

USE TestDatabase;
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

使用 OPENROWSET(BULK...) 和 XML 格式檔案

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL：

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

共用方式為

使用格式檔案跳過資料欄位 (SQL Server)

範例測試條件

範例資料表

範例資料檔案

建立格式檔案

建立非 XML 格式檔案

修改非 XML 格式檔案

使用 XML 格式檔案

修改 XML 格式檔案

使用格式檔案略過資料欄位來匯入資料

使用 bcp 和非 XML 格式檔案

使用 bcp 和 XML 格式檔案

使用 BULK INSERT 和非 XML 格式檔案

使用 BULK INSERT 和 XML 格式檔案

使用 OPENROWSET(BULK...) 和非 XML 格式檔案

使用 OPENROWSET(BULK...) 和 XML 格式檔案

下一步

意見反應

其他資源