The file storage type prompt offers you choices about how to store the data in the file. You can copy data into a file as:
Its database table type,
A character string, or
Any datatype for which implicit conversion is supported.
bcp copies
user-defined datatypes as their base types.
Table 4-3 shows the default storage type for each Adaptive Server datatype and the abbreviations that are acceptable to bcp.
For the most compact storage, use the default value.
For character files, use char.
Keep in mind that the date storage type is the Adaptive Server internal storage format of datetime, not the host operating system format of the date.
timestamp data is treated as binary(8).
In Table 4-3, brackets [ ] indicate that you can use the initial character or the beginning characters of the word. For example, for “bit” you can use “b,” “bi,” or “bit.”
Table datatype |
Storage type |
|---|---|
char, varchar |
c[har] |
text |
T[ext] |
int |
i[nt] |
smallint |
s[mallint] |
tinyint |
t[inyint] |
float |
f[loat] |
money |
m[oney] |
bit |
b[it] |
datetime |
d[atetime] |
binary, varbinary, timestamp |
x |
image |
I[mage] |
smalldatetime |
D |
real |
r |
smallmoney |
M |
numeric |
n |
decimal |
e |
To display this list while using bcp interactively, type a question mark (?) in response to the prompt “Enter the file storage type”.
The suggested values that appear in the prompts are the defaults. Remember that your response determines how the data is stored in the output file; you need not indicate the column’s type in the database table.
bcp fails if you enter a type that is not either implicitly convertible or char. For example, you may not be able to use smallint for int data (you may get overflow errors), but you can use int for smallint.
When storing noncharacter datatypes as their database types, bcp writes the data to the file in Adaptive Server’s internal data representation format for the host operating system, rather than in human-readable form.
Before copying data that is in character format from a file into a database table, check the datatype entry rules in the Reference Manual. Character data copied into the database with bcp must conform to those rules. Note especially that dates in the undelimited (yy)yymmdd format may result in overflow errors if the year is not specified first.
When you send host data files to sites that use terminals different from your own, inform them of the datafile_charset that you used to create the files.
