IMPORT COLUMNS 

You can use the IMPORT COLUMNS command to import individual column values of a table.

Prerequisites

You require the TESTDB demo database. Create the database user MONA if not set up already. Create the HOTEL schema as database user MONA if it does not exist. Create the table in the HOTEL schema if it does not exist yet.

You have exported the required data using the EXPORT COLUMNS Command.

Start the Loader as database user MONA and log on to the demo database TESTDB. Use the session mode.

loadercli –d testdb –u mona,red –s hotel

Examples

The data is to be imported into the CUSTOMER table in the HOTEL schema.

You can make entries after the keyword DATA to select the application data you would like to import.

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 the fact. Typical error messages include: -25451 and -25392. If these errors occur, the import is terminated at this point.

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
  DATA INSTREAM 'customer_csv.data' 

The application data (DATA) is imported in the CSV format (default value).

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

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

if_condition

You can select individual data records from the DATA data stream and then import only these records to the specified table. Observe 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'

The 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. Observe 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, Duplicates Clause (IMPORT COLUMNS Command)

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 the processing with an error.

Handling LONG Values

If you use the CSV and FWV [BINARY] formats, extra data streams are created for the LONG data during export.

The data of the HOTEL table in the HOTEL schema is to be imported. The HOTEL table has the LONG column INFO, the contents of which were exported in a separate data stream. Enter the LOB syntax elements after the DATA syntax element.

IMPORT COLUMNS TABLE hotel.hotel
  DATA INSTREAM 'hotel_csv.data'
  LOB INSTREAM INFO 'hotel_info_csv.long'

All the columns in the HOTEL table are imported. The LONG values are stored in the data stream hotel_info_csv.long.

See also:

Loader, Exporting and Importing LONG Values

Complete Syntax of the IMPORT COLUMNS Command

Combining the syntax options can result in correspondingly complex commands. Observe 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.

See also:

Other commands for Importing