使用格式檔案略過資料欄位 (SQL Server)Use a Format File to Skip a Data Field (SQL Server)

資料檔所包含的欄位,可以比資料表中的資料行數多。A data file can contain more fields than the number of columns in the table. 此主題描述如何將資料表資料行對應到相對的資料欄位並忽略多餘欄位,藉以修改非 XML 格式檔案與 XML 格式檔案,讓資料檔能容納更多欄位。This topic describes modifying both non-XML and XML format files to accommodate a data file with more fields by mapping the table columns to the corresponding data fields and ignoring the extra fields. 如需其他資訊,請參閱 建立格式檔案 (SQL Server)Please review Create a Format File (SQL Server) for additional information.

外框Outline
範例測試條件Example Test Conditions
 ● 範例資料表 ● Sample Table
 ● 範例資料檔案 ● Sample Data File
建立格式檔案Creating the Format Files
 ● 建立非 XML 格式檔案 ● Creating a Non-XML Format File
 ● 修改非 XML 格式檔案 ● Modifying a Non-XML Format File
 ● 建立 XML 格式檔案 ● Creating an XML Format File
 ● 修改 XML 格式檔案 ● Modifying an XML Format File
使用格式檔案略過資料欄位來匯入資料Importing Data with a Format File to skip a Data Field
 ● 使用 BCP 和非 XML 格式檔案 ● Using bcp and Non-XML Format File
 ● 使用 BCP 和 XML 格式檔案 ● Using bcp and XML Format File
 ● 使用 BULK INSERT 和非 XML 格式檔案 ● Using BULK INSERT and Non-XML Format File
 ● 使用 BULK INSERT 和 XML 格式檔案 ● Using BULK INSERT and XML Format File
 ● 使用 OPENROWSET(BULK...) 和非 XML 格式檔案 ● Using OPENROWSET(BULK...) and Non-XML Format File
 ● 使用 OPENROWSET(BULK...) 和 XML 格式檔案 ● Using OPENROWSET(BULK...) and XML Format File

注意

您可以透過下列項目,使用非 XML 或 XML 格式檔案,將資料檔案大量匯入至資料表:bcp 公用程式命令、BULK INSERT 陳述式或 INSERT ...SELECT * FROM OPENROWSET(BULK...) 陳述式。Either a non-XML or XML format file can be used to bulk import a data file into the table by using a bcp utility command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement. 如需詳細資訊,請參閱使用格式檔案大量匯入資料 (SQL Server)For more information, see Use a Format File to Bulk Import Data (SQL Server).

範例測試條件Example Test Conditions

本主題中的修改格式檔案範例是以下面定義的資料表和資料檔案為基礎。The examples of modified format files in this topic are based on the table and data file defined below.

範例資料表Sample Table

下列指令碼會建立測試資料庫和名為 myTestSkipField的資料表。The script below creates a test database and a table named myTestSkipField. 在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

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

範例資料檔案Sample Data File

建立空白檔案 D:\BCP\myTestSkipField.bcp ,並插入下列資料:Create an empty file D:\BCP\myTestSkipField.bcp and insert the following data:

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

建立格式檔案Creating the Format Files

若要從 myTestSkipField.bcp 大量匯入資料到 myTestSkipField 資料表中,格式檔案必須執行下列工作:To bulk import data from myTestSkipField.bcp into the myTestSkipField table, the format file must do the following:

  • 將第一個資料欄位對應到第一個資料行 PersonIDMap the first data field to the first column, PersonID.
  • 略過第二個資料欄位。Skip the second data field.
  • 將第三個資料欄位對應到第二個資料行 FirstNameMap the third data field to the second column, FirstName.
  • 將第四個資料欄位對應到第三個資料行 LastNameMap the fourth data field to the third column, LastName.

建立格式檔案的最簡單方法是使用 bcp 公用程式The simplest method to create the format file is by using the bcp utility. 首先,從現有的資料表建立基底格式檔案。First, create a base format file from the existing table. 其次,修改基底格式檔案以反映實際的資料檔案。Second, modify the base format file to reflect the actual data file.

建立非 XML 格式檔案Creating a Non-XML Format File

如需詳細資訊,請參閱 非 XML 格式檔案 (SQL Server)Please review Non-XML Format Files (SQL Server) for detailed information. 下列命令將使用 bcp 公用程式 ,根據 myTestSkipField.fmt的結構描述產生非 XML 格式檔案 myTestSkipFieldThe following command will use the bcp utility to generate a non-xml format file, myTestSkipField.fmt, based on the schema of myTestSkipField. 另外還會使用限定詞 c 來指定字元資料、使用 t, 來指定逗號作為欄位結束字元,並使用 T 來指定使用整合式安全性的信任連接。In addition, the qualifier c is used to specify character data , t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. 請在命令提示字元之下,輸入下列命令:At a command prompt, enter the following command:

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

修改非 XML 格式檔案 Modifying the Non-XML Format File

請參閱 非 XML 格式檔案的結構 以了解此術語。See Structure of Non-XML Format Files for terminology. 在 [記事本] 中開啟 D:\BCP\myTestSkipField.fmt 並執行下列修改:Open D:\BCP\myTestSkipField.fmt in Notepad and perform the following modifications: 1) 複製 FirstName 的整個格式檔資料列,並將它直接貼到下一行的 FirstName 後面。Copy the entire format-file row for FirstName and paste it directly after FirstName on the next line. 2) 針對新資料列和所有後續資料列,將主機檔案欄位順序值加一。Increase the host file field order value by one for the new row and all subsequent rows. 3) 增加資料行數目值,以反映資料檔案中的實際欄位數目。Increase the number of columns value to reflect the actual number of fields in the data file. 3) 針對第二個格式檔案資料列,將伺服器資料行順序從 2 修改為 0Modify the server column order from 2 to 0 for the second format-file row.

比較進行的變更:Compare the changes made:
之前Before

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

AfterAfter

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

修改的格式檔案現在會反映:The modified format file now reflects:

  • 4 個資料欄位4 data fields
  • myTestSkipField.bcp 中的第一個資料欄位會對應到第一個資料行、 myTestSkipField.. PersonIDThe first data field in myTestSkipField.bcp is mapped to the first column, myTestSkipField.. PersonID
  • myTestSkipField.bcp 中的第二個資料欄位未對應到任何資料行。The second data field in myTestSkipField.bcp is not mapped to any column.
  • myTestSkipField.bcp 中的第三個資料欄位會對應到第二個資料行、 myTestSkipField.. FirstNameThe third data field in myTestSkipField.bcp is mapped to the second column, myTestSkipField.. FirstName
  • myTestSkipField.bcp 中的第四個資料欄位會對應到第三個資料行、 myTestSkipField.. LastNameThe fourth data field in myTestSkipField.bcp is mapped to the third column, myTestSkipField.. LastName

建立 XML 格式檔案 Creating an XML Format File

如需詳細資訊,請參閱 XML 格式檔案 (SQL Server)Please review XML Format Files (SQL Server) for detailed information. 下列命令將使用 bcp 公用程式 ,根據 myTestSkipField.xml的結構描述建立 XML 格式檔案 myTestSkipFieldThe following command will use the bcp utility to create an xml format file, myTestSkipField.xml, based on the schema of myTestSkipField. 另外還會使用限定詞 c 來指定字元資料、使用 t, 來指定逗號作為欄位結束字元,並使用 T 來指定使用整合式安全性的信任連接。In addition, the qualifier c is used to specify character data , t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. 必須使用 x 限定詞來產生 XML 格式檔案。The x qualifier must be used to generate an XML-based format file. 請在命令提示字元之下,輸入下列命令:At a command prompt, enter the following command:

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

修改 XML 格式檔案 Modifying the XML Format File

請參閱 XML 格式檔案的結構描述語法 以了解此術語。See Schema Syntax for XML Format Files for terminology. 在 [記事本] 中開啟 D:\BCP\myTestSkipField.xml 並執行下列修改:Open D:\BCP\myTestSkipField.xml in Notepad and perform the following modifications: 1) 複製整個第二個欄位,並將它直接貼到下一行的第二個欄位後面。Copy the entire second field and paste it directly after the second field on the next line. 2) 針對新的 FIELD 以及每個後續 FIELD,將 "FIELD ID" 值加 1。Increase the "FIELD ID" value by 1 for the new FIELD and for each subsequent FIELD. 3) 針對 FirstNameLastName ,將 "COLUMN SOURCE" 值加 1,以反映修改過的對應。Increase the "COLUMN SOURCE" value by 1 for FirstName, and LastName to reflect the revised mapping.

比較進行的變更:Compare the changes made:
之前Before

\<?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>

AfterAfter

\<?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>

修改的格式檔案現在會反映:The modified format file now reflects:

  • 4 個資料欄位4 data fields
  • 對應到 COLUMN 1 的 FIELD 1 會對應到第一個資料表資料行、 myTestSkipField.. PersonIDFIELD 1 which corresponds to COLUMN 1 is mapped to the first table column, myTestSkipField.. PersonID
  • FIELD 2 未對應到任何 COLUMN,因此未對應至任何資料表資料行。FIELD 2 does not correspond to any COLUMN and thus, is not mapped to any table column.
  • 對應到 COLUMN 3 的 FIELD 3 會對應到第二個資料表資料行、 myTestSkipField.. FirstNameFIELD 3 which corresponds to COLUMN 3 is mapped to the second table column, myTestSkipField.. FirstName
  • 對應到 COLUMN 4 的 FIELD 4 會對應到第三個資料表資料行、 myTestSkipField.. LastNameFIELD 4 which corresponds to COLUMN 4 is mapped to the third table column, myTestSkipField.. LastName

使用格式檔案略過資料欄位來匯入資料Importing Data with a Format File to skip a Data Field

下列範例會使用上面建立的資料庫、資料檔案和格式檔案。The examples below use the database, datafile, and format files created above.

使用 bcp非 XML 格式檔案Using bcp and Non-XML Format File

請在命令提示字元之下,輸入下列命令:At a command prompt, enter the following command:

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

使用 bcpXML 格式檔案Using bcp and XML Format File

請在命令提示字元之下,輸入下列命令:At a command prompt, enter the following command:

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

使用 BULK INSERT非 XML 格式檔案Using BULK INSERT and Non-XML Format File

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

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 INSERTXML 格式檔案Using BULK INSERT and XML Format File

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

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 格式檔案Using OPENROWSET(BULK...) and Non-XML Format File

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

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 格式檔案Using OPENROWSET(BULK...) and XML Format File

在 Microsoft SQL Server Management Studio (SSMS) 中執行下列 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

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;

另請參閱See Also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
使用格式檔案略過資料表資料行 (SQL Server) Use a Format File to Skip a Table Column (SQL Server)
使用格式檔案將資料表資料行對應至資料檔欄位 (SQL Server) Use a Format File to Map Table Columns to Data-File Fields (SQL Server)