共用方式為

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

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

資料檔所包含的欄位，可以比資料表中的資料行數多。 本文描述修改非 XML 和 XML 格式檔案，藉由將數據表數據行對應至對應的數據欄位並忽略額外欄位，以容納具有更多字段的數據檔。

如需詳細資訊，請參閱使用 bcp 建立格式檔案（SQL Server）。

注意

非 XML 或 XML 格式的檔案可用於透過 bcp 公用程式 命令、BULK INSERT（Transact-SQL） 語句或 INSERT ... SELECT * FROM OPENROWSET BULK（Transact-SQL） 語句，將數據檔大量匯入至資料表中。 如需詳細資訊，請參閱 使用格式檔案大容量匯入資料 （SQL Server） 。

注意

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

範例測試條件

本文中修改格式檔案的範例是以範例數據表 myTestSkipField 和數據檔 D:\BCP\myTestSkipField.bcp為基礎。 將程式代碼範例中的本機檔案位置變更為計算機上的檔案位置。

範例資料表

腳本會建立測試資料庫和名為的 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 並執行下列修改：

  1. 複製 FirstName 的整個格式檔資料列，並將它直接貼到下一行的 FirstName 後面。
  2. 針對新資料列和所有後續資料列，將主機檔案欄位順序值加一。
  3. 增加資料行數目值，以反映資料檔案中的實際欄位數目。
  4. 針對第二個格式檔案資料列，將伺服器資料行順序從 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 並執行下列修改：

  1. 複製整個第二個欄位，並將它直接貼到下一行的第二個欄位後面。
  2. 將 FIELD ID 的值增加 1，適用於新的 FIELD 和每個後續的 FIELD。
  3. 將 COLUMN SOURCE 的值加 1 以符合 FirstName，並藉由 LastName 反映修訂後的對應。

比較進行的變更：

之前

<?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 個資料欄位
  • FIELD 與 COLUMN 1 對應的 1 被映射到第一個表格欄，myTestSkipField.. PersonID
  • FIELD 2 不會對應至任何 COLUMN ，因此不會對應至任何數據表數據行。
  • FIELD 對應到 COLUMN 3 的 3 會映射到第二個表格列，myTestSkipField.. FirstName
  • FIELD 對應至 COLUMN 4 的 4 被映射到第三個表格欄位， myTestSkipField.. LastName

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

這些範例使用本文中建立的範例資料庫、數據檔和格式檔案。

使用 bcp 和非 XML 格式檔案

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

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

使用 bcp 和 XML 格式檔案 （SQL Server）

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

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

使用 BULK INSERT （Transact-SQL） 和非 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 （Transact-SQL） 和 XML 格式檔案 （SQL Server）

在 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 （Transact-SQL） 和非 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（Transact-SQL） 和 XML 格式檔案（SQL Server）

在 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;

相關內容