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, 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.

Format File Options for Bulk-Import Commands

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

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

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 create 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

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

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:

DROP TABLE myTestFormatFiles

See Also

Concepts

Using a Format File to Map Fields to Columns During Bulk Import
Understanding Non-XML Format Files
Understanding XML Format Files

Other Resources

bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance