SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Utilities
HP NonStop SQL/MX Reference Manual—540440-003
5-31
Considerations for import
column allows null. Some employees have no middle initial. As a result, the input file
contains records like this:
2961,Mary,,Smith,143,3490,80000.00
To insert null in a nullable target column, you can specify two consecutive field
delimiters as shown in the preceding example. To insert blanks in the target column,
you can specify two field delimiters with the appropriate number of blanks between the
delimiters.
Default Values for Delimited Data Input Files
For a delimited input file, if a column in the target table does not allow null, using two
consecutive field delimiters directs import to use the default value for the column
(instead of null for the column). For example, a column defined as NOT NULL might
have space as its default value. In this case, two consecutive field delimiters in the
corresponding input field specify that space is to be stored in the target column.
Null Input Values for Fixed-Width Files
For a fixed-width input file, if a column in the target table allows null, you can specify a
null indicator character for that column in the input file. See [FIXED WIDTH FORMAT]
on page 5-27.
For example, suppose that the EMPLOYEE target table begins with the columns
EMPNUM, FIRST_NAME, MIDDLE_INITIAL, and LAST_NAME. Some employees
have no middle initial. You specify a hyphen (-) as the null indicator. As a result, the
input file contains records like this:
296 Mary -Smith 143 349080000.00
Null or Default Flag
For a delimited or fixed-width input file, if a column in the target table allows null or has
a default value, you can specify a null or default flag for that column in the input file.
The [COLUMN FORMAT] section of the format file indicates that this flag is used in the
input file. See [COLUMN FORMAT]
on page 5-27. For details about importing into
nullable columns, see import and Nullable Columns
on page 5-34.
These column definitions, for example, allow these insert values:
Column Definition Insert Value
NOT NULL NO DEFAULT Input value must be provided. Null or default value is
not allowed.
NOT NULL DEFAULT 'abc' If flag is D, default value is inserted. Otherwise, input
value is inserted. Null is not allowed.
NO DEFAULT If flag is N, null is inserted. Otherwise, input value is
inserted. Default value is not allowed.
DEFAULT 'abc' If flag is D, default value is inserted. If flag is N, null is
inserted. Otherwise, input value is inserted.