ASA SQL User's Guide
Importing and Exporting Data
Importing and exporting data
The structure of the data you want to load into a table does not always match the structure of the destination table itself, which may present problems during importing. 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 have several options. You can rearrange the columns in your table using the LOAD TABLE statement; you can rearrange the import data to fit the table using a variation of the INSERT statement and a global temporary table; or you can 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, to load two columns into the employee table, and set the remaining column values to the default values if there are any, the LOAD TABLE statement should look like this:
LOAD TABLE employee (emp_lname, emp_fname) FROM 'new_employees.txt' DEFAULTS ON
You can rearrange the import data to fit the table using a variation of the INSERT statement and a global temporary table.
To load data with a different structure using a global temporary table
In the SQL Statements pane of the Interactive SQL window, 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 FROM SELECT clause to extract and summarize data from the temporary table and put it into one or more permanent database tables.