Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
A data file can contain fields arranged in a different order from the corresponding columns in the table. This article presents both non-XML and XML format files that are modified to accommodate a data file whose fields are arranged in a different order from the table columns. The modified format file maps the data fields to their corresponding table columns. Review Create a format file with bcp (SQL Server) for additional information.
Either a non-XML or XML format file can be used to bulk import a data file into the table, using a bcp utility command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET statement. For more information, see Use a format file to bulk import data (SQL Server).
Note
This syntax, including bulk insert, isn't supported in Azure Synapse Analytics. In Azure Synapse Analytics and other cloud database platform integrations, accomplish data movement via the COPY statement in Azure Data Factory, or by using T-SQL statements such as COPY INTO and PolyBase.
The examples of modified format files in this article are based on the table and data file defined later in this article.
The following Transact-SQL script creates a test database and a table named myRemap
.
CREATE DATABASE TestDatabase;
GO
USE TestDatabase;
CREATE TABLE myRemap
(
PersonID SMALLINT,
FirstName VARCHAR (25),
LastName VARCHAR (30),
Gender CHAR (1)
);
The following data presents FirstName
and LastName
in the reverse order as presented in the table myRemap
. Using a text editor such as Notepad, create an empty file D:\BCP\myRemap.bcp
and insert the following data:
1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F
To bulk import data from myRemap.bcp
into the myRemap
table, the format file must do the following actions:
PersonID
.LastName
.FirstName
.Gender
.The simplest method to create the format file is by using the bcp utility. First, create a base format file from the existing table. Second, modify the base format file to reflect the actual data file.
Review Use Non-XML format files (SQL Server) for detailed information. The following command uses the bcp utility to generate a non-XML format file, myRemap.fmt
, based on the schema of myRemap
. In addition, the qualifier c
is used to specify character data, t,
is used to specify a comma as a field terminator, and T
is used to specify a trusted connection using integrated security. At a command prompt, enter the following command:
bcp TestDatabase.dbo.myRemap format nul -c -f D:\BCP\myRemap.fmt -t, -T
For information about terminology, see Structure of Non-XML Format Files. Open D:\BCP\myRemap.fmt
in Notepad and perform the following modifications:
myRemap.bcp
.Compare the changes:
Before
13.0
4
1 SQLCHAR 0 7 "," 1 PersonID ""
2 SQLCHAR 0 25 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 30 "," 3 LastName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "\r\n" 4 Gender SQL_Latin1_General_CP1_CI_AS
After
13.0
4
1 SQLCHAR 0 7 "," 1 PersonID ""
2 SQLCHAR 0 30 "," 3 LastName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 25 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "\r\n" 4 Gender SQL_Latin1_General_CP1_CI_AS
The modified format file now reflects:
myRemap.bcp
is mapped to the first column, myRemap.. PersonID
myRemap.bcp
is mapped to the third column, myRemap.. LastName
myRemap.bcp
is mapped to the second column, myRemap.. FirstName
myRemap.bcp
is mapped to the fourth column, myRemap.. Gender
Review XML Format Files (SQL Server) for detailed information. The following command uses the bcp utility to create an xml format file, myRemap.xml
, based on the schema of myRemap
. In addition, the qualifier c
is used to specify character data, t,
is used to specify a comma as a field terminator, and T
is used to specify a trusted connection using integrated security. The x
qualifier must be used to generate an XML-based format file. At a command prompt, enter the following command:
bcp TestDatabase.dbo.myRemap format nul -c -x -f D:\BCP\myRemap.xml -t, -T
Review Schema syntax for XML format files for terminology. Open D:\BCP\myRemap.xml
in Notepad and perform the following modifications:
The order in which the <FIELD>
elements are declared in the format file, is the order in which those fields appear in the data file. Thus, reverse the order for the <FIELD>
elements with ID attributes 2 and 3.
Ensure the <FIELD>
ID attribute values are sequential.
The order of the <COLUMN>
elements in the <ROW> element defines the order in which the bulk operation sends them to the target. The XML format file assigns each <COLUMN>
element a local name that has no relationship to the column in the target table of a bulk import operation. The order of the <COLUMN>
elements is independent of the order of <FIELD>
elements in a <RECORD> definition. Each <COLUMN>
element corresponds to a <FIELD>
element (whose ID is specified in the SOURCE attribute of the <COLUMN>
element). Thus, the values for <COLUMN>
SOURCE are the only attributes that require revision. Reverse the order for <COLUMN>
SOURCE attributes 2 and 3.
Compare the changes:
Before
<?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="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>
After
<?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="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>
The modified format file now reflects:
myRemap.. PersonID
myRemap.. LastName
myRemap.. FirstName
myRemap.. Gender
The following examples use the database, datafile, and format files created previously.
At a command prompt, enter the following command.
bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.fmt -T
At a command prompt, enter the following command.
bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.xml -T
Run the following Transact-SQL script.
USE TestDatabase;
GO
TRUNCATE TABLE myRemap;
BULK INSERT dbo.myRemap FROM 'D:\BCP\myRemap.bcp'
WITH (FORMATFILE = 'D:\BCP\myRemap.fmt');
GO
-- review results
SELECT *
FROM TestDatabase.dbo.myRemap;
Run the following Transact-SQL script.
USE TestDatabase;
GO
TRUNCATE TABLE myRemap;
BULK INSERT dbo.myRemap FROM 'D:\BCP\myRemap.bcp'
WITH (FORMATFILE = 'D:\BCP\myRemap.xml');
GO
-- review results
SELECT *
FROM TestDatabase.dbo.myRemap;
Run the following Transact-SQL script.
USE TestDatabase;
GO
TRUNCATE TABLE myRemap;
INSERT INTO dbo.myRemap
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myRemap.bcp',
FORMATFILE = 'D:\BCP\myRemap.fmt'
) AS t1;
GO
-- review results
SELECT *
FROM TestDatabase.dbo.myRemap;
Run the following Transact-SQL script.
USE TestDatabase;
GO
TRUNCATE TABLE myRemap;
INSERT INTO dbo.myRemap
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myRemap.bcp',
FORMATFILE = 'D:\BCP\myRemap.xml'
) AS t1;
GO
-- review results
SELECT *
FROM TestDatabase.dbo.myRemap;
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Import data in different formats for use in scripts by using Windows PowerShell cmdlets - Training
This module explains how to import data from a text file, CSV file, XML file, and JavaScript Object Notation (JSON) file.