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