Data can be copied into a database from program variables or from a flat file on the client’s host machine.
When you are copying data into a database table, the chief advantage of bulk copy over the alternative SQL insert command is speed. Also, SQL insert requires that the data be in character string format, while bulk copy can transfer native datatypes.
When copying data into a non-indexed table, the “high speed” version of bulk copy is used, which means that no data logging is performed during the transfer. If the system fails before the transfer is complete, no new data will remain in the database. Because high-speed transfer affects the recoverability of the database, it is only enabled if the Adaptive Server option select into/bulkcopy has been turned on. If the option is not enabled, and a user tries to copy data into a table that has no indexes, Adaptive Server generates an error message.
After the bulk copy is complete, the System Administrator should dump the database to ensure its future recoverability.
When you copy data into an indexed table, a slower version of bcp is automatically used, and row inserts are logged.
To copy data into a database, a DB-Library/C application must perform the following introductory steps:
Call dblogin to acquire a LOGINREC structure for later use with dbopen.
Call BCP_SETL to set up the LOGINREC for bulk copy operations into the database.
Call dbopen to establish a connection with Adaptive Server.
Call bcp_init to initialize the bulk copy operation and inform Adaptive Server whether the copy will be performed from program variables or from a host file. To copy data into the database, the bcp_init direction parameter must be passed as DB_IN.
At this point, an application copying data from program variables will need to perform different steps than an application copying data from a host file.
To copy data from program variables, a DB-Library/C application must perform the following steps in addition to the introductory ones listed previously:
Call bcp_bind once for each program variable that is to be bound to a database column.
Transfer a batch of data in a loop:
Assign program variables the data values to transfer.
Call bcp_sendrow to send the row of data.
After a batch of rows has been sent, call bcp_batch to save the rows in Adaptive Server.
After all the data has been sent, call bcp_done to end the bulk copy operation.
To copy data from a host file, a DB-Library/C application needs to perform the following steps in addition to the introductory ones listed previously:
Call bcp_control to set the batch size and change control parameter default settings.
Call bcp_columns to set the total number of columns found in the host file.
Call bcp_colfmt once for each column in the host file. If the host file matches the database table exactly, an application does not have to call bcp_colfmt.
Call bcp_exec to start the copy in.
