Article
10/01/2010

Using a Format File to Bulk Import Data

This topic illustrates the use of a format file in bulk-import operations. The format file maps the fields of the data file to the columns of the table. In Microsoft SQL Server 2005 and later versions, you can use a non-XML or XML format file to bulk import data when using a bcp command or a BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...) Transact-SQL command.

Important

For a format file to work with a Unicode character data file, all the input fields must be Unicode text strings (that is, either fixed-size or character-terminated Unicode strings).

Note

If you are unfamiliar with format files, see Understanding Non-XML Format Files and Understanding XML Format Files.

Format File Options for Bulk-Import Commands

The following table summarizes the format-file option of for each of the bulk-import commands.

Bulk-load Command	Using the Format-File Option
BULK INSERT	FORMATFILE = 'format_file_path'
INSERT ... SELECT * FROM OPENROWSET(BULK...)	FORMATFILE = 'format_file_path'
bcp … in	-fformat_file

For more information, see bcp Utility, BULK INSERT (Transact-SQL), or OPENROWSET (Transact-SQL).

Note

To bulk export or import SQLXML data, use one of the following data types in your format file: SQLCHAR or SQLVARYCHAR (the data is sent in the client code page or in the code page implied by the collation), SQLNCHAR or SQLNVARCHAR (the data is sent as Unicode), or SQLBINARY or SQLVARYBIN (the data is sent without any conversion).

Examples

The examples in this section illustrate how to use format files to bulk-import data by using the bcp command and the BULK INSERT, and INSERT ... SELECT * FROM OPENROWSET(BULK...) statements. Before you can run one of the bulk-import examples, you need to create a sample table, data file, and a format file.

Sample Table

The examples require that a table named myTestFormatFiles table be created in the AdventureWorks sample database under the dbo schema. To create this table, in SQL Server Management Studio Query Editor, execute:

USE AdventureWorks;
GO
CREATE TABLE myTestFormatFiles (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50),
   Col4 nvarchar(50)
   );
GO

Sample Data File

The examples use a sample data file, myTestFormatFiles-c.Dat, which contains the following records. To create the data file, at the Microsoft Windows command prompt, enter:

10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4

Sample Format Files

Some of the examples in this section use an XML format file, myTestFormatFiles-f-x-c.Xml, and other examples use a non-XML format file. Both format files use character data formats and a non-default field terminator (,).

The Sample Non-XML Format File

The following example uses bcp to generate an XML format file from the myTestFormatFiles table. The myTestFormatFiles.Fmt file contains the following information:

9.0
4
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       100     ","      2     Col2         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     Col3         SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "\r\n"   4     Col4         SQL_Latin1_General_CP1_CI_AS

To use bcp with the format option to create this format file, at the Windows command prompt, enter:

bcp AdventureWorks..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

For more information about creating a format file, see Creating a Format File.

The Sample XML Format File

The following example uses bcp to create to generate an XML format file from the myTestFormatFiles table. The myTestFormatFiles.Xml file contains the following information:

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

To use bcp with the format option to create this format file, at the Windows command prompt, enter:

bcp AdventureWorks..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

Using bcp

The following example uses bcp to bulk import data from the myTestFormatFiles-c.Dat data file into HumanResources.myTestFormatFiles table in the AdventureWorks sample database. This example uses an XML format file, MyTestFormatFiles.Xml. The example deletes any existing table rows before importing the data file.

At the Windows command prompt, enter:

bcp AdventureWorks..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T

Note

For more information about this command, see bcp Utility.

Using BULK INSERT

The following example uses BULK INSERT to bulk import data from the myTestFormatFiles-c.Dat data file into HumanResources.myTestFormatFiles table in the AdventureWorks sample database. This example uses a non-XML format file, MyTestFormatFiles.Fmt. The example deletes any existing table rows before importing the data file.

In SQL Server Management Studio Query Editor, execute:

USE AdventureWorks;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.Dat' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');
GO
SELECT * FROM myTestFormatFiles;
GO

Note

For more information about this statement, see BULK INSERT (Transact-SQL).

Using the OPENROWSET Bulk Rowset Provider

The following example uses INSERT ... SELECT * FROM OPENROWSET(BULK...) to bulk import data from the myTestFormatFiles-c.Dat data file into HumanResources.myTestFormatFiles table in the AdventureWorks sample database. This example uses an XML format file, MyTestFormatFiles.Xml. The example deletes any existing table rows before importing the data file.

In SQL Server Management Studio Query Editor, execute:

USE AdventureWorks;
DELETE myTestFormatFiles;
GO
INSERT INTO myTestFormatFiles
    SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.Dat',
      FORMATFILE='C:\myTestFormatFiles.Xml'     
      ) as t1 ;
GO
SELECT * FROM myTestFormatFiles;
GO

When you finish using the sample table, you can drop it using the following statement: