Merging different table structures

Use a variation of the INSERT statement and a global temporary table to rearrange the import data to fit the table.

Prerequisites

To create a global temporary table, you must have one of the following system privileges:

CREATE TABLE
CREATE ANY TABLE
CREATE ANY OBJECT

The privileges required to import (load) data depend on the settings of the -gl database option, as well as the source of the data you are importing from. See the LOAD TABLE statement for more information about the privileges required to load data.

To use the INSERT statement, you must be the owner of the table or have one of the following privileges:

INSERT ANY TABLE system privilege
INSERT privilege on the table

Additionally, if the ON EXISTING UPDATE clause is specified, you must have the UPDATE ANY TABLE system privilege or UPDATE privilege on the table.

Task

In the SQL Statements pane, create a global temporary table with a structure matching that of the input file.

You can use the CREATE TABLE statement to create the global temporary table.
Use the LOAD TABLE statement to load your data into the global temporary table.

When you close the database connection, the data in the global temporary table disappears. However, the table definition remains. You can use it the next time you connect to the database.
Use the INSERT statement with a SELECT clause to extract and summarize data from the temporary table and copy the data into one or more permanent database tables.

Results

The data is loaded into a permanent database table.

Example

The following is an example of the steps outline above.

CREATE GLOBAL TEMPORARY TABLE TempProducts
(
    ID                    integer NOT NULL,
    Name                  char(15) NOT NULL,
    Description           char(30) NOT NULL,
    Size                  char(18) NOT NULL,
    Color                 char(18) NOT NULL,
    Quantity              integer NOT NULL,
    UnitPrice             numeric(15,2) NOT NULL,
    CONSTRAINT ProductsKey PRIMARY KEY (ID)
)
ON COMMIT PRESERVE ROWS;

LOAD TABLE TempProducts
FROM 'C:\\ServerTemp\\newProducts.csv'
SKIP 1;

INSERT INTO Products WITH AUTO NAME 
    (SELECT Name, Description, ID, Size, Color, Quantity, 
            UnitPrice * 1.25 AS UnitPrice 
    FROM TempProducts);

The price of the items in the global temporary table are adjusted upwards by 25% before inserting the rows into the Products table.

Merging different table structures

Task

See also