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 Customers table and then load new rows into the Customers table from a fictitious file called new_customers.txt using a LOAD TABLE statement like this:
ALTER TABLE Customers ALTER City DEFAULT 'Waterloo'; LOAD TABLE Customers ( Surname, GivenName, Street, State, Phone ) FROM 'new_customers.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.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|