Transfer (insert) data from one table to another table. Creating a Format File. Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) When you bulk import into a SQL Server table or bulk export data from a table, you can use a format file to a flexible system for writing data files that requires little or no editing to comply with other data formats or to read data files from other software programs.
SQL Server support two types of format file: non-XML format and XML format. Generally, XML and non-XML format files are interchangeable. Note The version of the bcp utility (Bcp.exe) used to read a format file must be the same as, or later than the version used to create the format file. This topic describes how to use the bcp utility to create a format file for a particular table. Creating a Non-XML Format File To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path.
Bcp table_or_view format nul -fformat_file_name Examples A. BULK INSERT (Transact-SQL) Applies to: SQL Server (all supported versions) Azure SQL Database Imports a data file into a database table or view in a user-specified format in SQL Server Transact-SQL Syntax Conventions Syntax Arguments database_name The database name in which the specified table or view resides. Schema_name Specifies the name of the table or view schema. schema_name is optional if the default schema for the user performing the bulk-import operation is schema of the specified table or view. Table_name Specifies the name of the table or view to bulk import data into. FROM 'data_file' Specifies the full path of the data file that contains data to import into the specified table or view.
Data_file must specify a valid path from the server on which SQL Server is running. BULK INSERT Sales.Orders FROM '\\SystemX\DiskZ\Sales\data\orders.dat'; Beginning with SQL Server 2017 (14.x), the data_file can be in Azure Blob Storage. Azure SQL Database only supports reading from Azure Blob Storage. BATCHSIZE = batch_size Note. 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. 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. 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). 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 ...
Sample Table The examples require that a table named myTestFormatFiles table be created in the AdventureWorks2012 sample database under the dbo schema. USE AdventureWorks2012; GO CREATE TABLE myTestFormatFiles ( Col1 smallint, Col2 nvarchar(50), Col3 nvarchar(50), Col4 nvarchar(50) ); GO Sample Data File Sample Format Files Using bcp. Understanding Non-XML Format Files. You can create a non-XML format file automatically by specifying the format option in a bcp command.
When you specify an existing format file in a bcp command, the command uses the values that are recorded in the format file and does not prompt you for the file storage type, prefix length, field length, or field terminator. You can create a format file for a particular data type such as character data or native data. You can create a non-XML format file that contains interactively specified attributes for each data field. For more information, see Specify Data Formats for Compatibility when Using bcp (SQL Server). [Top] A non-XML format file is a text file that has a specific structure. The non-XML format file contains information about the file storage type, prefix length, field length, and field terminator of every table column. The following illustration illustrates the format-file fields for a sample non-XML format file.