NeXTSTEP 3.3 (Developer)

home *** CD-ROM | disk | FTP | other *** search

/ NeXTSTEP 3.3 (Developer) / NeXT_Developer-3.3.iso / usr / sybase / doc / bcp.man < prev next >

Wrap

Text File | 1993-04-22 | 23.2 KB | 551 lines

1 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ NAME: bcp FUNCTION: Copies a database table to or from a host file in a user- specified format. SYNTAX: bcp [[database_name.]owner.]table_name {in | out} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n] [-c] [-t field-terminator] [-r row-terminator] bcp Version 4.0 -- 1/15/89 2 ______________________________________________________________________ [-U username] [-P password] [-I interface] [-S server] [-v] EXAMPLES: In the following example, bcp copies data from the publishers table to a file for later reloading into SQL Server. Defaults were accepted for all prompts by pressing RETURN. bcp pubs..publishers out publ_out Password: Enter the file storage type of field pub_id [char]: Enter prefix-length of field pub_id [0]: Enter length of field pub_id [4]: Enter field terminator [none]: Enter the file storage type of field pub_name [char]: Enter prefix-length of field pub_name [1]: 3 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ Enter length of field pub_name [40]: Enter field terminator [none]: Enter the file storage type of field city [char]: Enter prefix-length of field city [1]: Enter length of field city [20]: Enter field terminator [none]: Enter the file storage type of field state [char]: Enter prefix-length of field state [1]: Enter length of field state [2]: Enter field terminator [none]: Do you want to save this format information in a file? [Y-n] y Host filename [bcp.fmt]: pub_form Starting copy... bcp Version 4.0 -- 1/15/89 4 ______________________________________________________________________ 3 rows copied. Clock Time (ms.): total = 0 Avg = 0 (3.00 rows per sec.) The results go into publ_out. To copy this data back into SQL Server using the saved format file, pub_form, you would use the command: bcp pubs..publishers in publ_out -f pub_form PARAMETERS: database_name - is optional if the table being copied is in your default database. Otherwise, you must specify a database name. owner - is optional only if you own the table being copied. If 5 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ no owner is specified and you do not own a table of that name, the command fails. table_name - the name of the database table you want to copy. {in | out} - the direction of the copy. In indicates a copy from a file into the database table, while out is a copy to a file from the database table. datafile - the full path name of a host file. The path name can be from 1 to 255 characters in length. It can also indicate a tape drive name. -m maxerrors - the maximum number of errors before the copy is aborted. Each row that can't be built by bcp is thrown out and counted as one error. The default, used if this option is not included, is 10. -f formatfile - the full path name of a file with stored responses from a previous use of bcp on the same table; crea- tion of the format file is optional. Use this option only when you have already created a format file that you want to bcp Version 4.0 -- 1/15/89 6 ______________________________________________________________________ use for a copy in or out. After you answer bcp's format questions, it will ask you if you want to save your answers in a format file. The default file name is bcp.fmt. The bcp program can refer to a format file when copying data, so that you do not have to duplicate your previous format responses interactively. If this option is not used, bcp will query you for format information interactively. -e errfile - the full path name of an error file where bcp stores any rows that it was unable to transfer from the file to the database. Error messages from the bcp program go to the user's workstation. If this option is not used, no error file is created. -F firstrow - the number of the first row to copy (default is the first row). -L lastrow - the number of the last row to copy (default is the last row). -b batchsize - the number of rows per batch of data copied (the 7 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ default is to copy all the rows in one batch). -n - perform the copy operation using the data's native (data- base) datatypes as the default. This option does not prompt for each field; it uses the default values. -c - perform the copy operation with character type as the default. This option does not prompt for each field; it uses char as the default storage type, no prefixes, \t as the default field separator, and \n as the default row termina- tor. -t field-terminator - specify the default field terminator. -r row-terminator - specify the default row terminator. -U username - allows the user to specify a login name. -P password - allows the user to specify a password. If the -P option is not given, bcp prompts for a password. If the -P option is given at the end of the command line without any password, bcp uses the default password (NULL). -I interface - allows the user to specify the name and location bcp Version 4.0 -- 1/15/89 8 ______________________________________________________________________ of the interfaces file that can (optionally) be searched as part of the process of connecting to the SQL Server. The named file contains the name and network address of every available SQL Server on the network. If this option is not used, bcp looks for a file named interfaces. -S server - allows the user to specify the name of the SQL Server with which to connect. This is the name that SQL Server looks up in the interfaces file. -v - reports the current version of the bcp program. COMMENTS: o The bcp bulk copy program provides a convenient method for transferring data between a database table and a host file or tape. Data copied IN from a file is appended to an existing database table; data copied OUT to a file overwrites any previ- ous contents of the file. 9 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ o The bcp program performs a high-speed data transfer. It is capable of reading or writing files in a wide variety of for- mats. o The -n (native format) and -c (character format) command line options each provide specific default formats for the data being copied. Native format provides the most compact storage; character format creates ASCII files, with tabs between columns. o When neither of the -n or -c option is used, bcp will query the user for information about each column in the table to be copied and asks whether you want to save your format informa- tion in a file. o Upon completion, bcp informs you of the number of rows of data successfully copied, the number of rows (if any) that it could not copy, the total time the copy took, the average amount of time it took to copy one row (given in milliseconds), and the bcp Version 4.0 -- 1/15/89 10 ______________________________________________________________________ number of rows copied per second. o In order to use bcp, you must have a valid SQL Server account and the appropriate permissions on the database tables and files. o The bcp program is optimized to load data into tables that do not have indexes associated with them. When you copy data into a table that does have one or more indexes, a slower version of bcp is automatically used. However, the fast version of bcp inserts data without logging it, and requires the System Administrator or Database Owner to first set the select into/bulkcopy option on with the sp_dboption procedure. If the option is not set on and a user tries to copy data into a table that has no indexes, SQL Server generates an error message. You don't need to set this option in order to copy data out to a file, or in order to copy data 11 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ in to a table that does have indexes. A bulk copy in to tables that have indexes always uses the slower version of bcp, and row inserts are logged. WARNING ________________________________________________________ | | | Because bulk copy logs inserts into a table that has | | indexes, the log can grow very large. You can truncate | | the log with DUMP TRANsaction after the bulk copy com- | | pletes, after you have backed up your database with DUMP | | DATABASE. | |_______________________________________________________________| While the select into/bulkcopy option is on, you are not allowed to dump the transaction log. Issuing the DUMP TRANsaction statement produces an error message instructing you to use DUMP DATABASE bcp Version 4.0 -- 1/15/89 12 ______________________________________________________________________ instead. This table shows which version of bcp will be used when copying IN, the necessary settings for the select into/bulkcopy option, and whether the transaction log is kept and dumpable. select into/bulkcopy ON OFF fast bcp OK (no indexes on target table) DUMP TRAN prohibited prohibited slow bcp OK OK (one or more indexes) DUMP TRAN prohibited DUMP TRAN OK By default, the select into/bulkcopy option is off in newly created databases. To change the default situation, turn this option on in the Model Database. 13 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ The performance penalty for copying data into a table that has indexes in place may be severe. If you are copying in data that will increase the size of the table by 25%, it may be faster to drop all the indexes beforehand with DROP INDEX, copy the data into the table, and then recreate the indexes. WARNING _____________________________________________________________ | | | Your database must have free space equal to at least 120% of | | the size of the table in order to build or rebuild a clustered | | index. | |____________________________________________________________________| o When using bcp, the user is prompted for a password, if it was not supplied with the -P option. bcp Version 4.0 -- 1/15/89 14 ______________________________________________________________________ o After the password has been given, bcp prompts for information on each field in the specified table. Each prompt displays a default value, in brackets, which is accepted by pressing the RETURN key. The prompts include: the storage type a prefix-length the field length a field terminator o The defaults in brackets at each prompt represent reasonable defaults for the field in question. These default values depend on the datatype of the field. The following table shows the defaults and possible responses. 15 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ Prompt Default Provided Possible Responses Storage Type Database storage char to create or type for most read an ASCII file; fields; any SQL Server data- char for varchar; type where implicit binary for var- conversion is sup- binary. ported. Prefix Length 0 for fields defined with datatype 0 if no prefix is (not storage type) char desired; defaults and all fixed-length datatypes, are recommended in 1 for most other datatypes all other cases. 2 for binary and varbinary saved as char 4 for text and image bcp Version 4.0 -- 1/15/89 16 ______________________________________________________________________ Length Defined length for Default values, or char and varchar. greater, are recom- Defined length * 2 mended. for binary and var- binary saved as char. Maximum length needed to avoid truncation or data overflow for all other datatypes. 17 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ Field Terminator None. Up to 30 ASCII char- acters, or one of the following: \t tab \n newline \r carriage return \0 null terminator \\ backslash o Data can be copied into a file either as its native (database) data- type, or as any datatype for which implicit conversion is supported for the datatype in question. See the DB-Library manual page for bcp Version 4.0 -- 1/15/89 18 ______________________________________________________________________ dbconvert for details. Here are the default storage datatypes that correspond to SQL Server datatypes, and the legal abbreviations: Table DatatypeStorageType char c[har] varchar c[har] text T[ext] binary x varbinary x image I[mage] int i[nt] smallint s[mallint] tinyint t[inyint] float f[loat] 19 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ bit b[it] money m[oney] datetime d[ate] timestamp x |-Brackets ([]) indicate that you may use the initial character, or the beginning characters of the word, i.e. for bit you could use b, bi or bit. timestamp data is treated as binary(8). o Fields defined in the database as char and binary are always padded with spaces to the full length defined in the database. o When storing fields (except char and binary fields) as char instead of their database datatypes, they will take less file storage space with the default length and a prefix or a terminator. bcp can use either a terminator or a prefix to determine the most efficient use bcp Version 4.0 -- 1/15/89 20 ______________________________________________________________________ of storage space. The maximum amount of storage space required for each field is suggested by bcp as the default. A length prefix is a 1, 2, or 4-byte integer which represents the length of each data value, and immediately precedes each data value in the host file. A terminator is any character or string of char- acters. The tables below show the interaction of prefix-lengths, termina- tors, and field length on the information in the file. ``P'' is used to indicate the prefix in the stored table; ``T'' indicates the terminator, and appended spaces are shown with dashes, ``-''. ``...'' indicates that the pattern would repeat for each field. The field length is 8 for each column, and we're storing the 6-character field ``string'' each time. SQL Server char data 21 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ Prefix-length = 0 Prefix-length 1, 2 or 4 No terminator string--string--... Pstring--Pstring--... Terminator string--Tstring--T... Pstring--TPstring--T... Other datatypes converted to char storage Prefix-length = 0 Prefix-length 1, 2 or 4 No terminator string--string--... PstringPstring... Terminator stringTstringT... PstringTPstringT... o Note that the file storage type and length of a column do not have to be the same as the type and length of the column in the database table. (If types and formats being copied in are incompatible with the structure of the database table, the copy fails.) bcp Version 4.0 -- 1/15/89 22 ______________________________________________________________________ o User datatypes are copied as their base datatype or as any datatype for which implicit conversion is supported. o For char or varchar data, any length value is accepted. o If data in varchar and varbinary fields is longer than the length you specify for copy out, the data in the file is silently truncated at the specified length. o A terminator string can be up to 30 characters long; the most common terminators are a tab (entered as ``\t'' and used for all columns except the last one), a newline (entered as ``\n'' and used for the last field in a row). Other terminators are: \0 (the null termina- tor) and \\ (backslash) and \r (carriage return). o When a terminator or prefix is present, it affects the actual length of data transferred. If the length of an entry being copied out to a file is less than the storage length, it is followed immediately by the terminator, or the prefix for the next field. The entry is 23 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ not padded to the full storage length (char and binary data is returned from SQL Server already padded to the full length). o When copying in from a file, data is transferred until either the number of bytes indicated have been copied or the terminator is encountered. Once a number of bytes equal to the file storage length has been transferred, the rest of the data is flushed until the terminator is encountered. When no terminator is used, the table storage length is strictly observed. o To determine the file storage length, a good rule of thumb is to consider it to be the maximum amount of data to be transferred for the column, plus terminators and/or prefixes. o When data is copied into a table, any defaults defined for columns and user datatypes are observed. However, rules and triggers are ignored in order to load data at the fastest possible speed. o For the most efficient use of space when copying out to a file, bcp Version 4.0 -- 1/15/89 24 ______________________________________________________________________ accept the default prompts: copy all data in their table datatypes, use prefixes as indicated, do not use terminators, and accept the default lengths. o If you hand-created input scripts for use as format files with pre- 4.0 bcp, you can use the bcptrans program to create new format files for use with the new bcp. The syntax of bcptrans is: bcptrans [[database_name.]owner.]table-name [ -3] {in | out} file_name [errors] The conversion program produces two files: bcptrans.cmd contains the appropriate command line for the new bcp program. bcptrans.fmt con- tains the appropriate format file for the new bcp program. These two files can then be used as input to the new bcp. The -3 flag to bcptrans must be used to convert an old format file when you are planning to copy IN an old datafile you created using the 25 Version 4.0 -- 1/15/89 bcp ______________________________________________________________________ default type and length for float and datetime data. (Otherwise, the format file bcptrans creates will use the new default lengths for float and datetime data, which have been changed from 20 characters to 25 characters for float, and 26 characters for datetime). Do not use the -3 flag for converting format files for any data that does not match this specification. RETURNS: This routine returns 0 upon successful completion. Otherwise it returns 1.