Specifying an external ID column

To specify an external ID column to look up the value of a foreign key column, use the following syntax:

column_name EXT_ID [schema_name.[table_name.] ]ext_id_column

where:

EXT_ID

is used to specify that the column specified by ext_id_column is used to look up the rowid to be inserted into the column specified by column_name.

schema_name

is the name of the schema of the table that contains the foreign key column being specified as the external ID column.

table_name

is the name of the table that contains the foreign key column being specified as the external ID column.

ext_id_column

is the external ID column.

Example A

The following example uses a list of expressions to insert records. Each Insert statement adds one record to the database table. In this case, one record is added to the table emp. Values are specified for five columns. The remaining columns in the table are assigned the default value or NULL if no default value is specified.

INSERT INTO emp (last_name, 
                    first_name, 
                    emp_id, 
                    salary, 
                    hire_date)
VALUES ('Smith', 'John', 'E22345', 27500, {1999-04-06})

Example B

The following example uses a Select statement to insert records. The number of columns in the result of the Select statement must match exactly the number of columns in the table if no column list is specified, or it must match the number of column names specified in the column list. A new entry is created in the table for every row of the Select result.

INSERT INTO emp1 (first_name, 
                  last_name, 
                  emp_id, 
                  dept, 
                  salary)
SELECT first_name, last_name, emp_id, dept, salary FROM emp
WHERE dept = 'D050'

Example C

The following example uses a list of expressions to insert records and specifies an external ID column (a foreign key column) named accountId that references a table that has an external ID column named AccountNum.

INSERT INTO emp (last_name, 
                    first_name, 
                    emp_id, 
                    salary, 
                    hire_date,
                    accountId EXT_ID AccountNum)
VALUES ('Smith', 'John', 'E22345', 27500, {1999-04-06}, 0001)