Background documentationIMPORT COLUMNS Locate this document in the navigation structure

 

You can import the column values of a table exported from a database using the EXPORT COLUMNS command into a database table. Use the IMPORT COLUMNS command to do so.

Prerequisites

You can use the demo databases DEMODB and TESTDB. You have exported the required data from demo database DEMODB. In demo database TESTDB, create the data administrator MONA if you have not already done so. Start the Loader and log onto database TESTDB as database administrator MONA with the password RED.

Use the session mode: loadercli -d testdb -u mona,red

Create the HOTEL schema as database user MONA if this does not already exist. Create the table in the HOTEL schema if this does not yet exist.

Activities

The data of the CUSTOMER table in the HOTEL schema is to be imported. You can make entries after the keyword DATA to select the application data you would like to import.

Caution Caution

If you import a table, the Loader also creates all the objects and links (views, foreign key dependencies with other tables…) that are defined for the table. If the necessary objects are not present and the import therefore cannot be executed, the Loader notifies you of this fact. Typical error messages include -25451 and -25392. If these errors occur, the import is terminated at this point.

End of the caution.

Test the Loader behavior for different tables. If necessary, enter the required dependent objects into the TESTDB database. The CUSTOMER table, for example, is dependent on the CITY table.

Importing All the Application Data in a Table

You can import all the application data of a table.

IMPORT COLUMNS TABLE hotel.customer

    cno 1-5

    title 6-25

    firstname 26-45

    name 46-85

    zip 86-105

    address 106-145

  DATA INSTREAM 'customer_fwv.data' FWV

The application data (DATA) is imported in the FWV format.

IMPORT COLUMNS TABLE hotel.customer

    cno 1-5

    title 6-25

    firstname 26-45

    name 46-85

    zip 86-105

    address 106-145

  DATA INSTREAM 'customer_fwvbinary.data' FWV BINARY

All (TABLE) application data (DATA) is imported in the FWV BINARY format. The corresponding data stream must contain data for all the table’s columns. If not, the Loader reports an error.

See also: Loader, Data stream, CSV, FWV, FWV BINARY

Note Note

You can also use the IMPORT TABLE ... DATA ... command to import all column values of a table.

End of the note.
if_condition

You can select individual data records from the DATA data stream and then import only these records to the specified table. Note the sequence of the syntax elements. Enter the IF condition first.

IMPORT COLUMNS TABLE hotel.customer

  IF POS 1 > '4000'

  DATA INSTREAM 'hotel_csv.data'

Rows that have a value above 4000 in the CNO column are imported to the CUSTOMER table.

See also: Loader, Selecting Data Records

DUPLICATES Clause

Use the DUPLICATES clause to control the behavior if duplicate table rows occur. Note the sequence of the syntax elements. Enter the DUPLICATES clause after the IF condition.

IMPORT COLUMNS TABLE hotel.customer

  UPDATE DUPLICATES

  DATA INSTREAM 'customer_csv.data'

The application data of table CUSTOMER is imported and an existing row is overwritten with the imported data record.

See also: Loader, IMPORT COLUMNS Command, Duplicates Clause

Importing the Application Data from Specific Columns in a Table

You can import the individual column values of a table.

IMPORT COLUMNS TABLE hotel.customer

    cno 1

    title 2

    name 3

    zip 4

    address 5

  DATA INSTREAM 'customer_csv.data'

The specified columns of the CUSTOMER table are imported in the specified order. The Loader expects data in the data stream only for the specified columns, which have to be in the desired order for import. The other columns are imported either with their DEFAULT values or with the NULL value, depending on how the columns are defined.

You have to import data to all columns for which a DEFAULT or NULL value was not specified when the table was defined, and which are required to have values by an integrity condition. If one of the necessary columns is not specified, the Loader terminates processing with an error.

Handling LOB Values

If you use the CSV and FWV [BINARY] formats, extra data streams are created for the LOB data during export. The data of the HOTEL table in the HOTEL schema is to be imported. The HOTEL has the LOB column INFO, the contents of which were exported in a separate data stream. Enter the LOB syntax element after the DATA syntax element.

IMPORT COLUMNS TABLE hotel.hotel

  DATA INSTREAM 'hotel_csv.data'

  LOB INSTREAM INFO 'hotel_info_csv.lob'

All the columns in the HOTEL table are imported. The LOB values are stored in the data stream hotel_info_csv.lob.

See also: Loader, Importing LOB Values

Complete Syntax of the IMPORT COLUMNS Command

Combining the syntax options can result in correspondingly complex commands. Note the sequence of the syntax elements.

IMPORT COLUMNS TABLE hotel.customer

  IF POS 1 >= '4000'

  UPDATE DUPLICATES

    cno 1

    title 2

    name 3

    zip 4

    address 5

  DATA INSTREAM FILE 'customer_csv.data' CSV

For a complete syntax description, see IMPORT COLUMNS command in the Loader documentation.

More Information

Other commands for Importing

Session Mode