The structure of the source data does not need to match the structure of the destination table itself. For example, the column data types may be different or in a different order, or there may be extra values in the import data that do not match columns in the destination table.
If you know that the structure of the data you want to import does not match the structure of the destination table, you can:
provide a list of column names to be loaded in the LOAD TABLE statement
rearrange the import data to fit the table with a variation of the INSERT statement and a global temporary table
use the INPUT statement to specify a specific set or order of columns
If the file you are importing contains data for a subset of the columns in a table, or if the columns are in a different order, you can also use the LOAD TABLE statement DEFAULTS option to fill in the blanks and merge non-matching table structures.
If DEFAULTS is OFF, any column not present in the column list is assigned NULL. If DEFAULTS is OFF and a non-nullable column is omitted from the column list, the database server attempts to convert the empty string to the column's type.
If DEFAULTS is ON and the column has a default value, that value is used.
For example, you can define a default value for the City column in the Employees table and then load new rows into the Employees table using a LOAD TABLE statement like this:
ALTER TABLE Employees ALTER City DEFAULT 'Waterloo'; LOAD TABLE Employees (Surname, GivenName, EmployeeID, DepartmentID, StartDate) FROM 'new_employees.txt' DEFAULTS ON
Since a value is not provided for the City column, the default value is supplied. If
DEFAULTS OFF had been specified, the City column would have been assigned the empty string instead.