background preloader

Data Export / Import

Facebook Twitter

Transfer (insert) data from one table to another table. Creating a Format File. 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.

Creating a Format File

SQL Server support two types of format file: non-XML format and XML format. The non-XML format is the original format that is supported by earlier versions of SQL Server. Generally, XML and non-XML format files are interchangeable. However, we recommend that you use the XML syntax for new format files because they provide several advantages over non-XML format files. This topic describes how to use the bcp utility to create a format file for a particular table. 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 -f format_file_name Examples A. A. The bcp command contains the following qualifiers. B. C. D. BULK INSERT (Transact-SQL) Database_name Is the database name in which the specified table or view resides.


If not specified, this is the current database. schema_name Is 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. If schema is not specified and the default schema of the user performing the bulk-import operation is different from the specified table or view, SQL Server returns an error message, and the bulk-import operation is canceled. table_name Is the name of the table or view to bulk import data into. ' data_file ' Is 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. BATCHSIZE =batch_size Specifies the number of rows in a batch. Specifies that all constraints on the target table or view must be checked during the bulk-import operation. Using a Format File to Bulk Import Data. This topic illustrates the use of a format file in bulk-import operations.

Using a Format File to Bulk Import Data

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. Understanding Non-XML Format Files. You can create a non-XML format file automatically by specifying the format option in a bcp command.

Understanding Non-XML Format Files

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 following illustration illustrates the format-file fields for a sample non-XML format file. The Version and Number of columns fields occur one time only.