Training
Learning path
Implement finance and operations apps - Training
Plan and design your project methodology to successfully implement finance and operations apps with FastTrack services, data management and more.
This 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 more fields than the number of columns in the table. This topic describes modifying both non-XML and XML format files to accommodate a data file with more fields by mapping the table columns to the corresponding data fields and ignoring the extra fields. Please review Create a Format File (SQL Server) for additional information.
Note
Either a non-XML or XML format file can be used to bulk import a data file into the table by using a bcp utility command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement. For more information, see Use a Format File to Bulk Import Data (SQL Server).
Note
This syntax, including bulk insert, is not 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 topic are based on the table and data file defined below.
The script below creates a test database and a table named myTestSkipField
. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
CREATE DATABASE TestDatabase;
GO
USE TestDatabase;
CREATE TABLE myTestSkipField
(
PersonID smallint,
FirstName varchar(25),
LastName varchar(30)
);
Create an empty file D:\BCP\myTestSkipField.bcp
and insert the following data:
1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain
To bulk import data from myTestSkipField.bcp
into the myTestSkipField
table, the format file must do the following:
PersonID
.FirstName
.LastName
.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 Non-XML Format Files (SQL Server) for detailed information. The following command will use the bcp utility to generate a non-xml format file, myTestSkipField.fmt
, based on the schema of myTestSkipField
. 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.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T
Review the structure of non-XML format files for terminology. Open D:\BCP\myTestSkipField.fmt
in Notepad and perform the following modifications:
FirstName
and paste it directly after FirstName
on the next line.2
to 0
for the second format-file row.Compare the changes made:
Before
13.0
3
1 SQLCHAR 0 7 "," 1 PersonID ""
2 SQLCHAR 0 25 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 30 "\r\n" 3 LastName SQL_Latin1_General_CP1_CI_AS
After
13.0
4
1 SQLCHAR 0 7 "," 1 PersonID ""
2 SQLCHAR 0 25 "," 0 FirstName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 25 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "\r\n" 3 LastName SQL_Latin1_General_CP1_CI_AS
The modified format file now reflects:
myTestSkipField.bcp
is mapped to the first column, myTestSkipField.. PersonID
myTestSkipField.bcp
is not mapped to any column.myTestSkipField.bcp
is mapped to the second column, myTestSkipField.. FirstName
myTestSkipField.bcp
is mapped to the third column, myTestSkipField.. LastName
Review XML Format Files (SQL Server) for detailed information. The following command will use the bcp utility to create an xml format file, myTestSkipField.xml
, based on the schema of myTestSkipField
. 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.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T
Review schema syntax for XML format files for terminology. Open D:\BCP\myTestSkipField.xml
in Notepad and perform the following modifications:
FirstName
, and LastName
to reflect the revised mapping.Compare the changes made:
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="\r\n" MAX_LENGTH="30" 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"/>
</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="25" 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="30" 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="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
The modified format file now reflects:
myTestSkipField.. PersonID
myTestSkipField.. FirstName
myTestSkipField.. LastName
The examples below use the database, datafile, and format files created above.
At a command prompt, enter the following command:
bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T
At a command prompt, enter the following command:
bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T
Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField
FROM 'D:\BCP\myTestSkipField.bcp'
WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');
GO
-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;
Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField
FROM 'D:\BCP\myTestSkipField.bcp'
WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');
GO
-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;
Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myTestSkipField.bcp',
FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
) AS t1;
GO
-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;
Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):
USE TestDatabase;
GO
TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField
SELECT *
FROM OPENROWSET (
BULK 'D:\BCP\myTestSkipField.bcp',
FORMATFILE = 'D:\BCP\myTestSkipField.xml'
) AS t1;
GO
-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;
Training
Learning path
Implement finance and operations apps - Training
Plan and design your project methodology to successfully implement finance and operations apps with FastTrack services, data management and more.