建立格式檔案 (SQL Server)Create a Format File (SQL Server)

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse

當您將資料大量匯入 SQL ServerSQL Server 資料表,或從資料表大量匯出資料時,可以使用格式檔案提供可用來寫入資料檔的彈性系統,幾乎不需要進行編輯即可符合其他資料格式,或是從其他軟體程式讀取資料檔。When you bulk import into a SQL ServerSQL Server table or bulk export data from a table, you can use a format file to a flexible system for writing data files that requires little or no editing to comply with other data formats or to read data files from other software programs.

SQL ServerSQL Server 支援兩種類型的格式檔案:非 XML 格式和 XML 格式。support two types of format file: non-XML format and XML format. 非 XML 格式是舊版 SQL ServerSQL Server所支援的原始格式。The non-XML format is the original format that is supported by earlier versions of SQL ServerSQL Server.

一般而言,XML 和非 XML 格式檔案可以互換使用,Generally, XML and non-XML format files are interchangeable. 但是,仍建議您在新的格式檔案中使用 XML 語法,因為 XML 比非 XML 格式檔案多了一些優點。However, we recommend that you use the XML syntax for new format files because they provide several advantages over non-XML format files.

注意

用於讀取格式檔案的 bcp 公用程式 (Bcp.exe) 版本,必須與用於建立格式檔案的版本相同或比它更新。The version of the bcp utility (Bcp.exe) used to read a format file must be the same as, or later than the version used to create the format file. 例如,SQL Server 2012 (11.x)SQL Server 2012 (11.x) bcp 可以讀取由 SQL Server 2008SQL Server 2008bcp 產生的 10.0 版格式檔案,但 SQL Server 2008SQL Server 2008 bcp 無法讀取由 SQL Server 2012 (11.x)SQL Server 2012 (11.x) bcp 產生的 11.0 版格式檔案。For example, SQL Server 2012 (11.x)SQL Server 2012 (11.x) bcp can read a version 10.0 format file, which is generated by SQL Server 2008SQL Server 2008 bcp, but SQL Server 2008SQL Server 2008 bcp cannot read a version 11.0 format file, which is generated by SQL Server 2012 (11.x)SQL Server 2012 (11.x) bcp.

此主題描述如何使用 bcp 公用程式 來建立特定資料表的格式檔案。This topic describes how to use the bcp utility to create a format file for a particular table. 格式檔案以指定的資料類型選項 ( -n-c-w,或 -N) 與資料表或檢視分隔符號為基礎。The format file is based on the data-type option specified (-n, -c, -w,or -N) and the table or view delimiters.

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

使用 bcp 命令建立格式檔案時,請指定 format 引數並使用 nul 取代資料檔案路徑。To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. format 選項也需要 -f 選項,例如:The format option also requires the -f option, such as:

bcp table_or_view format nul -fformat_file_namebcp table_or_view format nul -fformat_file_name

注意

為了區分非 XML 格式檔案,建議您使用 .fmt 做為副檔名,例如 MyTable.fmt。To distinguish a non-XML format file, we recommend that you use .fmt as the file name extension, for example, MyTable.fmt.

如需有關非 XML 格式檔案之結構與欄位的詳細資訊,請參閱 非 XML 格式檔案 (SQL Server)所支援的原始格式。For information about the structure and fields of non-XML format files, see Non-XML Format Files (SQL Server).

範例Examples

本節包含下列範例,說明如何使用 bcp 命令建立非 XML 格式檔案:This section contains the following examples that show how to use bcp commands to create a non-XML format file:

  • A.A. 建立原生資料的非 XML 格式檔案Creating a non-XML format file for native data

  • B.B. 建立字元資料的非 XML 格式檔案Creating a non-XML format file for character data

  • C.C. 建立 Unicode 原生資料的非 XML 格式檔案Creating a non-XML format file for Unicode native data

  • D.D. 建立 Unicode 字元資料的非 XML 格式檔案Creating a non-XML format file for Unicode character data

  • F.F. 使用格式檔案與字碼頁選項Using a format file with the code page option

這些範例使用 HumanResources.Department 範例資料庫中的 AdventureWorks2012AdventureWorks2012 資料表。The examples use the HumanResources.Department table in the AdventureWorks2012AdventureWorks2012 sample database. HumanResources.Department 資料表包含四個資料行: DepartmentIDNameGroupNameModifiedDateThe HumanResources.Department table contains four columns: DepartmentID, Name, GroupName, and ModifiedDate.

A.A. 建立原生資料的非 XML 格式檔案Creating a non-XML format file for native data

下列範例會建立 Department-n.xml資料表的 XML 格式檔案 AdventureWorks2012AdventureWorks2012HumanResources.DepartmentThe following example creates an XML format file, Department-n.xml, for the AdventureWorks2012AdventureWorks2012HumanResources.Department table. 格式檔案使用原生資料類型。The format file uses native data types. 所產生之格式檔案的內容,會出現在命令之後。The contents of the generated format file are presented after the command.

bcp 命令包含下列限定詞。The bcp command contains the following qualifiers.

限定詞Qualifiers 描述Description
formatnul-f format_fileformatnul-f format_file 指定非 XML 格式檔案。Specifies the non-XML format file.
-n-n 指定原生資料類型。Specifies native data types.
-T-T 指定 bcp 公用程式使用整合式安全性的信任連接,連接至 SQL ServerSQL ServerSpecifies that the bcp utility connects to SQL ServerSQL Server with a trusted connection using integrated security. 如果未指定 -T ,則必須指定 -U-P ,才能順利登入。If -T is not specified, you must specify -U and -P to successfully log in.

在 Windows 命令提示字元中,輸入下列 bcp 命令:At the Windows command prompt, enter the following bcp command:

bcp AdventureWorks2012.HumanResources.Department format nul -T -n -f Department-n.fmt  

產生的格式檔案 Department-n.fmt包含下列資訊:The generated format file, Department-n.fmt, contains the following information:

12.0  
4  
1  SQLSMALLINT   0       2       ""   1     DepartmentID         ""  
2  SQLNCHAR      2       100     ""   2     Name                 SQL_Latin1_General_CP1_CI_AS  
3  SQLNCHAR      2       100     ""   3     GroupName            SQL_Latin1_General_CP1_CI_AS  
4  SQLDATETIME   0       8       ""   4     ModifiedDate         ""  

如需詳細資訊,請參閱 非 XML 格式檔案 (SQL Server)所支援的原始格式。For more information, see Non-XML Format Files (SQL Server).

B.B. 建立字元資料的非 XML 格式檔案Creating a non-XML format file for character data

下列範例會建立 Department.fmt資料表的 XML 格式檔案 AdventureWorks2012AdventureWorks2012HumanResources.DepartmentThe following example creates an XML format file, Department.fmt, for the AdventureWorks2012AdventureWorks2012HumanResources.Department table. 格式檔案使用字元資料格式和非預設欄位結束字元 (,)。The format file uses character data formats and a non-default field terminator (,). 所產生之格式檔案的內容,會出現在命令之後。The contents of the generated format file are presented after the command.

bcp 命令包含下列限定詞。The bcp command contains the following qualifiers.

限定詞Qualifiers 描述Description
formatnul-f format_fileformatnul-f format_file 指定非 XML 格式檔案。Specifies a non-XML format file.
-c-c 指定字元資料。Specifies character data.
-T-T 指定 bcp 公用程式使用整合式安全性的信任連接,連接至 SQL ServerSQL ServerSpecifies that the bcp utility connects to SQL ServerSQL Server with a trusted connection using integrated security. 如果未指定 -T ,則必須指定 -U-P ,才能順利登入。If -T is not specified, you must specify -U and -P to successfully log in.

在 Windows 命令提示字元中,輸入下列 bcp 命令:At the Windows command prompt, enter the following bcp command:

bcp AdventureWorks2012.HumanResources.Department format nul -c -f Department-c.fmt -T  

產生的格式檔案 Department-c.fmt包含下列資訊:The generated format file, Department-c.fmt, contains the following information:

12.0  
4  
1  SQLCHAR       0       7       "\t"     1     DepartmentID            ""  
2  SQLCHAR       0       100     "\t"     2     Name                    SQL_Latin1_General_CP1_CI_AS  
3  SQLCHAR       0       100     "\t"     3     GroupName               SQL_Latin1_General_CP1_CI_AS  
4  SQLCHAR       0       24      "\r\n"   4     ModifiedDate            ""  

如需詳細資訊,請參閱 非 XML 格式檔案 (SQL Server)所支援的原始格式。For more information, see Non-XML Format Files (SQL Server).

C.C. 建立 Unicode 原生資料的非 XML 格式檔案Creating a non-XML format file for Unicode native data

若要針對 HumanResources.Department 資料表的 Unicode 原生資料建立非 XML 格式檔案,請使用下列命令:To create a non-XML format file for Unicode native data for the HumanResources.Department table, use the following command:

bcp AdventureWorks2012.HumanResources.Department format nul -T -N -f Department-n.fmt  

如需如何使用 Unicode 原生資料的詳細資訊,請參閱使用 Unicode 原生格式匯入或匯出資料 (SQL Server)For more information about how to use Unicode native data, see Use Unicode Native Format to Import or Export Data (SQL Server).

D.D. 建立 Unicode 字元資料的非 XML 格式檔案Creating a non-XML format file For Unicode character data

若要針對使用預設結束字元之 HumanResources.Department 資料表的 Unicode 字元資料建立非 XML 格式檔案,請使用下列命令:To create a non-XML format file for Unicode character data for the HumanResources.Department table that uses default terminators, use the following command:

bcp AdventureWorks2012.HumanResources.Department format nul -T -w -f Department-w.fmt  

如需如何使用 Unicode 字元資料的詳細資訊,請參閱使用 Unicode 字元格式匯入或匯出資料 (SQL Server)For more information about how to use Unicode character data, see Use Unicode Character Format to Import or Export Data (SQL Server).

F.F. 使用格式檔案與字碼頁選項Using a format file with the code page option

若使用 bcp 命令 (也就是 bcp format) 建立格式檔案,就會在格式檔案中寫入定序/字碼頁的資訊。If you create a format file using the bcp command (that is, by using bcp format), information about the collation/code page will be written in the format file.
下列範例格式檔案屬於具 5 個資料行的資料表且包含定序。The following example format file for a table with 5 columns includes the collation.

13.0  
5  
1  SQLCHAR         0       0       "**\t**"         1     c_0          Cyrillic_General_CS_AS  
2  SQLCHAR         0       0       "**\t**"         2     c_1          Cyrillic_General_CS_AS  
3  SQLCHAR         0       3000    "**\t**"         3     c_2          Cyrillic_General_CS_AS  
4  SQLCHAR         0       5       "**\t**"         4     c_3          ""  
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4          ""  
  

如果您嘗試使用 bcp in -c -C65001 -f format_file ..." 或 "BULK INSERT/OPENROWSET ... FORMATFILE='format_file' CODEPAGE=65001 ...",將資料匯入至 SQL ServerSQL Server,則定序/字碼頁的相關資訊將會優先於 65001 選項。If you try to import data into SQL ServerSQL Server using bcp in -c -C65001 -f format_file ..." or "BULK INSERT/OPENROWSET ... FORMATFILE='format_file' CODEPAGE=65001 ...", information about the collation/code page will have priority over 65001 option.
因此,如果您產生格式檔案,則必須從產生的格式檔案手動刪除定序資訊,然後才開始將資料匯回 SQL ServerSQL ServerTherefore, if you generate a format file, you must manually delete the collation info from the generated format file before you start importing data back into SQL ServerSQL Server.
下列範例為不具定序資訊的格式檔案。The following is an example of the format file without the collation info.

13.0  
5  
1  SQLCHAR         0       0       "**\t**"         1     c_0              ""  
2  SQLCHAR         0       0       "**\t**"         2     c_1              ""  
3  SQLCHAR         0       3000    "**\t**"         3     c_2              ""  
4  SQLCHAR         0       5       "**\t**"         4     c_3              ""  
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4              ""  

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

使用 bcp 命令建立格式檔案時,請指定 format 引數並使用 nul 取代資料檔案路徑。To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. format 選項一律需要 -f 選項,您也必須指定 -x 選項才能建立 XML 格式檔案,例如:The format option always requires the -f option, and to create an XML format file, you must also specify the -x option, such as:

bcp table_or_view format nul-f format_file_name -xbcp table_or_view format nul-f format_file_name -x

注意

為了區分 XML 格式檔案,建議您使用 .xml 做為副檔名,例如 MyTable.xml。To distinguish an XML format file, we recommend that you use .xml as the file name extension, for example, MyTable.xml.

如需有關 XML 格式檔案之結構與欄位的詳細資訊,請參閱 XML 格式檔案 (SQL Server)所支援的原始格式。For information about the structure and fields of XML format files, see XML Format Files (SQL Server).

範例Examples

本節包含下列範例,說明如何使用 bcp 命令建立 XML 格式檔案:This section contains the following examples that show how to use bcp commands to create an XML format file:

  • A.A. 建立字元資料的 XML 格式檔案Creating an XML format file for character data
  • B.B. 建立原生資料的 XML 格式檔案Creating an XML format file for native data

這些範例使用 HumanResources.Department 範例資料庫中的 AdventureWorks2012AdventureWorks2012 資料表。The examples use the HumanResources.Department table in the AdventureWorks2012AdventureWorks2012 sample database. HumanResources.Department 資料表包含四個資料行: DepartmentIDNameGroupNameModifiedDateThe HumanResources.Department table contains four columns: DepartmentID, Name, GroupName, and ModifiedDate.

注意

Adventure Works Cycles 是虛構的製造公司,用於示範資料庫概念與案例。Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios.

A.A. 建立字元資料的 XML 格式檔案Creating an XML format file for character data

下列範例會建立 Department.xml資料表的 XML 格式檔案 AdventureWorks2012AdventureWorks2012HumanResources.DepartmentThe following example creates an XML format file, Department.xml, for the AdventureWorks2012AdventureWorks2012HumanResources.Department table. 格式檔案使用字元資料格式和非預設欄位結束字元 (,)。The format file uses character data formats and a non-default field terminator (,). 所產生之格式檔案的內容,會出現在命令之後。The contents of the generated format file are presented after the command.

bcp 命令包含下列限定詞。The bcp command contains the following qualifiers.

限定詞Qualifiers 描述Description
formatnul-f format_file -xformatnul-f format_file -x 指定 XML 格式檔案。Specifies the XML format file.
-c-c 指定字元資料。Specifies character data.
-t ,-t , 指定逗號 ( , ) 作為欄位結束字元。Specifies a comma (,) as the field terminator.

注意:如果資料檔使用預設欄位結束字元 (\t),則不需要 -t 參數。Note: If the data file uses the default field terminator (\t), the -t switch is unnecessary.
-T-T 指定 bcp 公用程式使用整合式安全性的信任連接,連接至 SQL ServerSQL ServerSpecifies that the bcp utility connects to SQL ServerSQL Server with a trusted connection using integrated security. 如果未指定 -T ,則必須指定 -U-P ,才能順利登入。If -T is not specified, you must specify -U and -P to successfully log in.

在 Windows 命令提示字元中,輸入下列 bcp 命令:At the Windows command prompt, enter the following bcp command:

bcp AdventureWorks2012.HumanResources.Department format nul -c -x -f Department-c.xml -t, -T  

產生的格式檔案 Department-c.xml包含下列 XML 元素:The generated format file, Department-c.xml, contains the following XML elements:

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>  
 </ROW>  
</BCPFORMAT>  

如需本格式檔案語法的相關資訊,請參閱 XML 格式檔案 (SQL Server)For information about the syntax of this format file, see XML Format Files (SQL Server). 如需字元資料的相關資訊,請參閱使用字元格式匯入或匯出資料 (SQL Server)For information about character data, see Use Character Format to Import or Export Data (SQL Server).

B.B. 建立原生資料的 XML 格式檔案Creating an XML format file for native data

下列範例會建立 Department-n.xml資料表的 XML 格式檔案 HumanResources.DepartmentThe following example creates an XML format file, Department-n.xml, for the HumanResources.Department table. 格式檔案使用原生資料類型。The format file uses native data types. 所產生之格式檔案的內容,會出現在命令之後。The contents of the generated format file are presented after the command.

bcp 命令包含下列限定詞。The bcp command contains the following qualifiers.

限定詞Qualifiers 描述Description
formatnul-f format_file -xformatnul-f format_file -x 指定 XML 格式檔案。Specifies the XML format file.
-n-n 指定原生資料類型。Specifies native data types.
-T-T 指定 bcp 公用程式使用整合式安全性的信任連接,連接至 SQL ServerSQL ServerSpecifies that the bcp utility connects to SQL ServerSQL Server with a trusted connection using integrated security. 如果未指定 -T ,則必須指定 -U-P ,才能順利登入。If -T is not specified, you must specify -U and -P to successfully log in.

在 Windows 命令提示字元中,輸入下列 bcp 命令:At the Windows command prompt, enter the following bcp command:

bcp AdventureWorks2012.HumanResources.Department format nul -x -f Department-n.xml -n -T  

產生的格式檔案 Department-n.xml包含下列 XML 元素:The generated format file, Department-n.xml, contains the following XML elements:

<?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="NativeFixed" LENGTH="2"/>  
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="4" xsi:type="NativeFixed" LENGTH="8"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>  
 </ROW>  
</BCPFORMAT>  

如需本格式檔案語法的相關資訊,請參閱 XML 格式檔案 (SQL Server)For information about the syntax of this format file, see XML Format Files (SQL Server). 如需如何使用原生資料的相關資訊,請參閱使用原生格式匯入或匯出資料 (SQL Server)For information about how to use native data, see Use Native Format to Import or Export Data (SQL Server).

將資料欄位對應至資料表資料行Mapping Data Fields to Table Columns

格式檔案由 bcp 建立後,便會依序描述所有的資料表資料行。As created by bcp, a format file describes all the table columns in order. 您可以修改格式檔案,重新排列或省略資料表資料列。You can modify a format file to rearrange or omit table rows. 這可讓您針對欄位未直接對應至資料表資料行的資料檔來自訂格式檔案。This lets you customize a format file to a data file whose fields do not map directly to the table columns. 如需詳細資訊,請參閱下列主題:For more information, see the following topics:

另請參閱See Also

bcp 公用程式 bcp Utility
使用格式檔案將資料表資料行對應至資料檔案欄位 (SQL Server) Use a Format File to Map Table Columns to Data-File Fields (SQL Server)
使用格式檔案略過資料表資料行 (SQL Server) Use a Format File to Skip a Table Column (SQL Server)
使用格式檔案略過資料欄位 (SQL Server) Use a Format File to Skip a Data Field (SQL Server)
非 XML 格式檔案 (SQL Server) Non-XML Format Files (SQL Server)
XML 格式檔案 (SQL Server)XML Format Files (SQL Server)