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.
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.
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
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.
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.
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
You can also use the IMPORT TABLE ... DATA ... command to import all column values of a table.
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
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
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.
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
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.