Using Data Files and Format Files
The simplest bulk copy program does the following:
- Calls bcp_init to specify bulk copying out (set BCP_OUT) from a table or view to a data file.
- Calls bcp_exec to execute the bulk copy operation.
The data file is created in native mode; therefore, data from all columns in the table or view are stored in the data file in the same format as in the database. The file can then be bulk copied into a server by using these same steps and setting DB_IN instead of DB_OUT. This works only if both the source and target tables have exactly the same structure. The resulting data file can also be input to the bcp utility by using the /n (native mode) switch.
To bulk copy out the result set of a Transact-SQL statement instead of directly from a table or view:
- Call bcp_init to specify bulk copying out, but specify NULL for the table name.
- Call bcp_control with eOption set to BCPHINTS and iValue set to a pointer to a SQLTCHAR string containing the Transact-SQL statement.
- Call bcp_exec to execute the bulk copy operation.
The Transact-SQL statement can be any statement that generates a result set. The data file is created containing the first result set of the Transact-SQL statement. Bulk copy ignores any result set after the first if the Transact-SQL statement generates multiple result sets (for example, if it contains COMPUTE or COMPUTE BY).
To create a data file in which column data is stored in a different format than in the table, call bcp_columns to specify how many columns will be changed, then call bcp_colfmt for each column whose format you want to change. This is done after calling bcp_init but before calling bcp_exec. bcp_colfmt specifies the format in which the column's data is stored in the data file. It can be used when bulk copying in or out. You can also use bcp_colfmt to set the row and column terminators. For example, if your data contains no tab characters, you can create a tab-delimited file by using bcp_colfmt to set the tab character as the terminator for each column.
When bulk copying out and using bcp_colfmt, you can easily create a format file describing the data file you have created by calling bcp_writefmt after the last call to bcp_colfmt.
When bulk copying in from a data file described by a format file, read the format file by calling bcp_readfmt after bcp_init but before bcp_exec.
The bcp_control function controls several options when bulk copying into SQL Server from a data file. bcp_control sets options, such as the maximum number of errors before termination, the row in the file on which to start the bulk copy, the row to stop on, and the batch size.
See Also
Concepts
Performing Bulk Copy Operations (ODBC)