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

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

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

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

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

The Sample XML Format File

<?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="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>

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

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.

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

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

Note:
For more information about the OPENROWSET BULK clause, see OPENROWSET (Transact-SQL).