How to write a dbload command file in delimiter form

Command files must contain required elements, including delimiters.

The FILE statement in the following example describes the stock.unl data rows as composed of six fields separated by a vertical bar (|) as the delimiter.

FILE stock.unl DELIMITER '|' 6;
INSERT INTO stock;

The last field on each line may optionally have a delimiter after it. Two consecutive delimiters define a null field.

1|SMT|baseball gloves|450.00|case|10 gloves/case
2|HRO|baseball|126.00|case|24/case
3|SHK|baseball bat|240.00|case|12/case

The example INSERT statement is minimal; it contains only the required elements. Because the column list is omitted, the INSERT statement implies that values are to be inserted into every column in the stock table in the sequence defined in the CREATE TABLE statement. Because the VALUES clause is omitted, the INSERT statement implies that the input values for every field are defined in the most recent FILE statement in the same order in the CREATE TABLE statement. This INSERT statement is valid because the stock table contains six columns, which correspond to the number of values that the FILE statement defines.

FILE stock.unl DELIMITER '|' 6;
INSERT INTO new_stock (col1, col2, col3, col5, col6)
   VALUES (f01, f03, f02, f05, 'autographed');

The user changed the column names, the order of the data, and the meaning of col6 in the new stock table. Because the fourth column in new_stock (col4) is not named in the column list, the new data row contains a null value in the col4 position (assuming that the column permits null values). If no default is specified for col4, the inserted value is null.

The following table shows the first data row that is inserted into new_stock from this INSERT statement.