t.
When you copy data into a table that has one or more indexes or triggers, a slower version of bcp is automatically used, which logs row inserts. This includes indexes implicitly created using the unique integrity constraint of a create table statement. However, bcp does not enforce the other integrity constraints defined for a table.
Because the fast version of bcp inserts data without logging it, the System Administrator or Database Owner must first set the system procedure sp_dboption, "DB", to true. If the option is not set to true, and you try to copy data into a table that has no indexes or triggers, Adaptive Server generates an error message. You do not need to set this option to copy data out to a file or to copy data into a table that contains indexes or triggers.
Because bcp logs inserts into a table that has indexes or triggers when you use slow bcp, the log can grow very large. You can truncate the log with dump transaction after the bulk copy completes and after you have backed up your database with dump database.
稺hile the select into/bulkcopy/pllsort option is on, you cannot dump the transaction log. Issuing dump transaction produces an error message instructing you to use dump database instead.
Warning! Be certain that you dump your database before you turn off the select into/bulkcopy/pllsort flag. If you have inserted unlogged data into your database, and you then perform a dump transaction before performing a dump database, you will not be able to recover your data.
稦ast bcp runs more slowly while a dump database is taking place.
稵able 4-2 shows which version bcp uses when copying in, the necessary settings for the select into/bulkcopy/pllsort option, and whether the transaction log is kept and can be dumped.
?
Table 4-2: Comparing fast and slow bcp
稡y default, the select into/bulkcopy/pllsort option is off in newly created databases. To change the default situation, turn this option on in the model database.
The performance penalty for copying data into a table that has indexes or triggers in place can be severe. If you are copying in a very large number of rows, it may be faster to drop all the indexes and triggers beforehand with drop index (or alter table, for indexes created as a unique constraint) and drop trigger; set the database option; copy the data into the table; re-create the indexes and triggers; and then dump the database. Remember to allocate disk space for the construction of indexes and triggers?about 2.2 times the amount of space needed for the data.
Responding to bcp Prompts
When you copy data in or out using the -n (native format) or -c (character format) parameters, bcp prompts you only for your password, unless you supplied it with the -P parameter. If you do not supply either the -n, -c or -f formatfile parameter, bcp prompts you for information for each field in the table or view.
稥ach prompt displays a default value, in brackets, which you can accept by pressing Return. The prompts include:
稵he file storage type, which can be character or any valid Adaptive Server datatype
稵he prefix length, which is an integer indicating the length in bytes of the following data
稵he storage length of the data in the file for non-NULL fields
稵he field terminator, which can be any character string
稴cale and precision for numeric and decimal data types
The row terminator is the field terminator of the last field in the table, view, or file.
稵he bracketed defaults represent reasonable values for the datatypes of the field in question. For the most efficient use of space when copying out to a file:
稶se the default prompts
稢opy all data in the datatypes defined by their table
稶se prefixes as indicated
稤o not use terminators
稟ccept the default lengths
Table 4-3 shows the bcp prompts, defaults, and the possible alternate user responses:
Table 4-3: bcp prompts?their defaults and user responses
穊cp can copy data out to a file either as its native (database) datatype or as any datatype for which implicit conversion is supported for the datatype in question. bcp copies user-defined datatypes as their base datatype or as any datatype for which implicit conversion is supported. For more information on datatype conversions, see dbconvert in the Open Client DB-Library/C Reference Manual or the Adaptive Server Reference Manual.
Be careful when you copy data from different operating systems because not all operating systems use the same native datatypes (for example, copying from NT into a UNIX server).
稟 prefix length is a 1-, 2-, or 4-byte integer that represents the length of each data value. It immediately precedes the data value in the host file.
稦ields defined in the database as char, nchar, and binary are always padded with spaces (null bytes for binary) to the full length defined in the database. timestamp data is treated as binary( .
If data in the varchar and varbinary fields is longer than the length specified for copy out, bcp silently truncates the data in the file at the specified length.
稟 field terminator string can be up to 30 characters long. The most common terminators are a tab (entered a上一页 [1] [2] [3] [4] [5] 下一页 |
