使用格式檔案將資料表資料行對應至資料檔欄位 (SQL Server)

文章
10/30/2024

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

資料檔中包含的欄位順序可以與資料表中對應資料行的順序不同。本文同時呈現非 XML 和 XML 格式檔案，這些檔案會修改以容納數據檔，其欄位會以與數據表數據行不同的順序排列。已修改的格式檔案可將資料欄位對應到與它們對應的資料表資料行。如需其他資訊，請參閱使用 bcp 建立格式檔案。

您可以使用 bcp 公用程式命令、BULK INSERT 語句或 INSERT ...，將非 XML 或 XML 格式檔案大容量導入資料表。SELECT * FROM OPENROWSET 語句。如需詳細資訊，請參閱使用格式檔案大容量匯入資料（SQL Server）。

注意

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

範例測試條件

本文中修改格式檔案的範例是以本文稍後定義的數據表和數據檔為基礎。

範例資料表

下列 Transact-SQL 腳本會建立測試資料庫和名為的 myRemap數據表。

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

CREATE TABLE myRemap
(
    PersonID SMALLINT,
    FirstName VARCHAR (25),
    LastName VARCHAR (30),
    Gender CHAR (1)
);

範例資料檔案

下列數據會以反向順序呈現 FirstName ， LastName 如下表 myRemap所示。使用記事本之類的文字編輯器，建立空白檔案 D:\BCP\myRemap.bcp 並插入下列資料：

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

建立格式檔案

若要將資料大容量myRemap.bcpmyRemap導入資料表，格式檔案必須執行下列動作：

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

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

建立非 XML 格式檔案

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

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

修改非 XML 格式檔案

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

重新排列格式檔案資料列的順序，使數據列的順序與中的數據 myRemap.bcp順序相同。
確定主機檔案欄位的順序值是連續的。
請確定最後一個格式檔案數據列之後有歸位字元。

比較下列變更：

之前

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

之後

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

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

myRemap.bcp 中的第一個資料欄位會對應到第一個資料行 myRemap.. PersonID
myRemap.bcp 中的第二個資料欄位會對應到第三個資料行 myRemap.. LastName
myRemap.bcp 中的第三個資料欄位會對應到第二個資料行 myRemap.. FirstName
myRemap.bcp 中的第四個資料欄位會對應到第四個資料行 myRemap.. Gender

使用 XML 格式檔案

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

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

修改 XML 格式檔案

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

專案在格式檔案中宣告的順序 <FIELD> ，是那些字段出現在數據檔中的順序。因此，反轉標識碼屬性為 2 和 3 的項目 <FIELD> 順序。
確定標識碼屬性值是循序的 <FIELD> 。
ROW> 元素中的<項目順序<COLUMN>會定義大量作業傳送至目標的順序。 XML 格式檔案會將每個 <COLUMN> 元素指派一個與大容量導入作業之目標數據表中數據行沒有關聯性的本機名稱。項目的順序<COLUMN>與 RECORD> 定義中的<項目順序<FIELD>無關。每個 <COLUMN> 元素都會對應至 <FIELD> 元素（其標識元是在元素的 <COLUMN> SOURCE屬性中指定）。因此，SOURCE 的值 <COLUMN> 是唯一需要修訂的屬性。反轉SOURCE屬性2和3的順序 <COLUMN> 。

比較下列變更：

之前

<?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="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" 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"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</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="30" 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="1" 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="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

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

對應到 COLUMN 1 的 FIELD 1 會對應到第一個資料表資料行 myRemap.. PersonID
對應至 COLUMN 2 的 FIELD 2 會重新對應至第三個數據表數據行， myRemap.. LastName
對應至 COLUMN 3 的 FIELD 3 會重新對應至第二個數據表數據行， myRemap.. FirstName
對應到 COLUMN 4 的 FIELD 4 會對應到第四個資料表資料行 myRemap.. Gender

使用格式檔案匯入資料，將資料表資料行對應至資料檔案欄位

下列範例會使用先前建立的資料庫、數據檔和格式檔案。

使用 bcp 和非 XML 格式檔案

在命令提示字元中，輸入下列命令。

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

使用 bcp 和 XML 格式檔案

在命令提示字元中，輸入下列命令。

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

使用 BULK INSERT 和非 XML 格式檔案

執行下列 Transact-SQL 腳本。

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

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

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

使用 BULK INSERT 和 XML 格式檔案

執行下列 Transact-SQL 腳本。

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

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

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

使用 OPENROWSET 和非 XML 格式檔案

執行下列 Transact-SQL 腳本。

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

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

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

使用 OPENROWSET 和 XML 格式檔案

執行下列 Transact-SQL 腳本。

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

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

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

分享方式：

使用格式檔案將資料表資料行對應至資料檔欄位 (SQL Server)

範例測試條件

範例資料表

範例資料檔案

建立格式檔案

建立非 XML 格式檔案

修改非 XML 格式檔案

使用 XML 格式檔案

修改 XML 格式檔案

使用格式檔案匯入資料，將資料表資料行對應至資料檔案欄位

使用 bcp 和非 XML 格式檔案

使用 bcp 和 XML 格式檔案

使用 BULK INSERT 和非 XML 格式檔案

使用 BULK INSERT 和 XML 格式檔案

使用 OPENROWSET 和非 XML 格式檔案

使用 OPENROWSET 和 XML 格式檔案

意見反映

更多資源

分享方式：

使用格式檔案將資料表資料行對應至資料檔欄位 (SQL Server)

範例測試條件

範例資料表

範例資料檔案

建立格式檔案

建立非 XML 格式檔案

修改非 XML 格式檔案

使用 XML 格式檔案

修改 XML 格式檔案

使用格式檔案匯入資料，將資料表資料行對應至資料檔案欄位

使用 bcp 和非 XML 格式檔案

使用 bcp 和 XML 格式檔案

使用 BULK INSERT 和非 XML 格式檔案

使用 BULK INSERT 和 XML 格式檔案

使用 OPENROWSET 和非 XML 格式檔案

使用 OPENROWSET 和 XML 格式檔案

相關內容

意見反映

更多資源