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

適用于:SQL Server (所有支援的版本) Azure SQL Database 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 語句來完成資料移動。

範例測試條件

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

範例資料表

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

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myRemap
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   Gender char(1)
   );

範例資料檔案

下面的資料會依照 FirstName 資料表所示,以相反順序顯示 LastNamemyRemap。 使用 [記事本] 建立空白檔案 D:\BCP\myRemap.bcp ,並插入下列資料:

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

建立格式檔案

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

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

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

建立非 XML 格式檔案

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

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

修改非 XML 格式檔案

請參閱 非 XML 格式檔案的結構 以了解此術語。 在 [記事本] 中開啟 D:\BCP\myRemap.fmt 並執行下列修改:

  1. 重新排列格式檔案資料列的順序,讓資料列的順序與 myRemap.bcp中的資料順序相同。
  2. 確定主機檔案欄位的順序值是連續的。
  3. 確定最後一個格式檔案資料列後面沒有歸位字元。

比較下列變更:

之前

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的結構描述建立 XML 格式檔案 myRemap。 另外還會使用限定詞 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 並執行下列修改:

  1. 在格式檔案中宣告 FIELD > 元素的順序是那些欄位出現在資料檔案中的順序 < ,因此會反轉 ID 屬性為 2 和 3 的 < FIELD > 元素順序。
  2. <確定欄位 > 識別碼屬性值是循序的。
  3. ROW > 元素中 < COLUMN > 元素的順序 < 會定義大量作業將它們傳送至目標的順序。 XML 格式檔案會將每個 < COLUMN > 元素指派為與大量匯入作業之目標資料表中資料行沒有關聯性的本機名稱。 COLUMN 元素的順序 < 與 RECORD > 定義中的 < FIELD > 元素順序 < 無關。 > 每個 < COLUMN > 元素都會對應至 < FIELD > 元素 (,其識別碼是在 COLUMN > 元素的 < SOURCE 屬性中指定) 。 因此,COLUMN > SOURCE 的值 < 是唯一需要修訂的屬性。 反轉 COLUMN > SOURCE 屬性 2 和 3 的順序 < 。

比較下列變更:

之前

<?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="," 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="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="," 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

使用 bcpXML 格式檔案

請在命令提示字元之下,輸入下列命令:

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

使用 BULK INSERT 和非 XML 格式檔案

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 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 INSERTXML 格式檔案

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 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 (BULK...) 和非 XML 格式檔案

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 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 (BULK...) XML 格式檔案

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 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;

後續步驟