Format Files for Importing or Exporting Data (SQL Server)
When you bulk import data into a SQL Server table or bulk export data from a table, you can use a format file to store all the format information that is required to bulk export or bulk import data. This includes format information for each field in a data file relative to that table.
SQL Server 2012 supports two types of format files: XML formats and non-XML format files. Both non-XML format files and XML format files contain descriptions of every field in a data file, and XML format files also contain descriptions of the corresponding table columns. Generally, XML and non-XML format files are interchangeable. However, we recommend that you use the XML syntax for new format files because they provide several advantages over non-XML format files. For more information, see XML Format Files (SQL Server).
In this Topic:
Benefits of Format Files
Examples of Format Files
When Is a Format File Required?
Related Tasks
Benefits of Format Files
Provides 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.
Enables you to bulk import data without having to add or delete unnecessary data or to reorder existing data in the data file. Format files are particularly useful when a mismatch exists between fields in the data file and columns in the table.
Examples of Format Files
The following examples show the layout of a non-XML format file and of an XML format file. These format files correspond to the HumanResources.myTeam table in the AdventureWorks2012 sample database. This table contains four columns: EmployeeID, Name, Title, and ModifiedDate.
Note
For information about this table and how to create it, see HumanResources.myTeam Sample Table (SQL Server).
A. Using a non-XML format file
The following non-XML format file uses the SQL Server native data format for the HumanResources.myTeam table. This format file was created by using the following bcp command.
bcp AdventureWorks.HumanResources.myTeam format nul -f myTeam.Fmt -n -T
The contents of this format file are as follows: 9.0
4
1 SQLSMALLINT 0 2 "" 1 EmployeeID ""
2 SQLNCHAR 2 100 "" 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 100 "" 3 Title SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 100 "" 4 Background SQL_Latin1_General_CP1_CI_AS
For more information, see Non-XML Format Files (SQL Server).
[Top]
B. Using an XML format file
The following XML format file uses the SQL Server native data format for the HumanResources.myTeam table. This format file was created by using the following bcp command.
bcp AdventureWorks.HumanResources.myTeam format nul -f myTeam.Xml -x -n -T
The format file contains:
<?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="NativePrefix" LENGTH="1"/>
<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="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="EmployeeID" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="Title" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="Background" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
For more information, see XML Format Files (SQL Server).
[Top]
When Is a Format File Required?
An INSERT ... SELECT * FROM OPENROWSET(BULK...) statement always requires a format file.
- For bcp or BULK INSERT, in simple situations, using a format file is optional and rarely necessary. However, for complex bulk-import situations, a format file is frequently required.
Format files are required if:
The same data file is used as a source for multiple tables that have different schemas.
The data file has a different number of fields that the target table has columns; for example:
The target table contains at least one column for which either a default value is defined or NULL is allowed.
The users do not have SELECT/INSERT permissions on one or more columns in the table.
A single data file is used with two or more tables that have different schemas.
The column order is different for the data file and table.
The terminating characters or prefix lengths differ among the columns of the data file.
Note
In the absence of a format file, if a bcp command specifies a data-format switch (-n, -c, -w, or -N) or a BULK INSERT operation specifies the DATAFILETYPE option, the specified data format is used as the default method of interpreting the fields of the data file.
[Top]
Related Tasks
[Top]