Use format files

Use format files

When using the interactive mode to copy large data volumes,BCPThe utility prompts you to store information about the storage type, prefix length, field length, and field Terminator and line terminator. A file used to store the format information of each field in a data file is called a format file:

Do you want to save this format information in a file? [Y/n] yHost filename: [bcp.fmt]

Although the default format file name is BCP. FMT, you can also specify other file names.

You can use this format to provide default information when you copy large volumes of data in a data file back to a Microsoft SQL server instance, or copy large volumes of data from a table at other times, you do not have to specify the format again. When you use an existing format file to copy large data volumes to or from an SQL server instance,BCPThe input file storage type, prefix length, field length, and field Terminator are not prompted, becauseBCPUse the recorded value.

When importing data to an SQL server instance, useBCPUtility-FSwitch, or the formatfile clause of the Bulk insert statement. For example, if you want to use the previously created format file (authors. FMT) to copy the content in the new_auth.dat data file to a large capacityPubsDatabaseAuthors2Table, you can use the following command:

bcp pubs..authors2 in c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword

The bulk insert statement can be used.BCPThe format file saved by the utility. For example:

BULK INSERT pubs..authors2 FROM 'c:\new_auth.dat' WITH (FORMATFILE = 'c:\authors.fmt')

A format file is a text file with a special structure separated by tabs.

The following table describes the file format structure.

Field	Description
Version	BCP.
Number of fields	The number of fields in the data file. This number must be the same for all rows.
Host file field order	The location of each field in the data file. The first field in the row is 1, and so on.
Host file data type	The data type stored in a specific field of the data file. For ASCII data files, use sqlchar; for local format data files, use the default data type. For more information, see File Storage types.
Prefix length	The number of prefix characters in the field length. Valid prefixes are 0, 1, 2, and 4. To avoid specifying a length prefix, set it to 0. If the field contains a null value, you must specify the length prefix. For more information, see prefix length.
Data Length of the host file	The maximum length (in bytes) of the Data Type stored in a specific field of the data file ). For more information, see field length.
Terminator	Delimiter used to separate fields in a data file. The commonly used Terminator is a comma (,), a tab (\ t), and a line terminator (\ r \ n ). For more information, see the field Terminator.
Server column Sequence	The order in which columns appear in the SQL Server table. For example, if the fourth field in the data file is mapped to the sixth column in the SQL Server table, the server column order of the fourth field is 6. When receiving data in the data file, to ignore a column in the table, set the server column sequence value to 0.
Server column name	Name of the column obtained from the SQL Server table. You do not need to use the actual name of the field. The only condition is that the field in the format file is not empty.
Sorting rules	Sorting rules are used to store character and Unicode data in large-capacity copy data files.

DescriptionIf a field does not exist in the data file, you can set the prefix length, field length, and server column sequence to 0 and specify no Terminator, skip this column when importing a column to a table. This method actually indicates that this data field does not exist in the data file, and the server column should not load its data.

 

Selectively copy data

The format file provides a method to selectively copy large volumes of data in the data file to the SQL server instance. In this way, if the fields in the data file do not match the columns in the table, data can still be transmitted to the table. This method can be used when fields in the data file have the following conditions:

• The number is less than the number of columns in the table.

• More than the number of columns in the table.
• The order of the columns in the table is different.

By using a format file, you can copy large data volumes to an SQL server instance without adding or deleting unnecessary data or sorting existing data in the data file.

The subsequent three topics contain examples of selectively copying data. To execute the subsequent examples, you must firstPubsCreate in DatabaseAuthorsA copy of the table namedAuthors2. To create a copyAuthorsRun the following command to copy a table:

USE pubsGOSELECT * INTO authors2 FROM authorsGO