Create new table

The Data Loader has an option to create the table being loaded. You select this by checking the Create new table checkbox (which is on the right of the table name).

When this option is selected, AQT will create the target table at the start of the load process.

Definition of the target Table

The left grid on the mapping tab shows the details of the table which will be created and loaded.

• When you are loading from either a table or query AQT will get the column definitions from the table / query you are loading from.

  If you are loading from a table/query in a different database type, AQT will convert the column definitions between the two database types. This work well in most cases but is not perfect; if you wish to map data types in a particular way you can the Type Mapping option (described later).

• When the loading from either a file or Excel worksheet, AQT will get the column names and column types by parsing the file.

  AQT will do this automatically when the table is being loaded. However an alternative is for you to click on the Scan button. When you do this, the file will be parsed and the column name / type and null-indicator displayed in the grid.

In both cases, you will have the option to amend the column names / types to more suitable values as required.

Options

There are a number of options (on the Options tab) which are relevant when using Create new table mode:

• Review SQL before creating. When this is checked, you have a chance the review (and amend) the Create Table statement before it is run.
• Drop Table before creating. When this is checked, AQT will drop and create the table.
• Tablespace. The tablespace the table will be created in. Only used for Oracle and DB2.
• Database. The database the table will be created in. Only used for DB2 z/OS. When this is specified the In Database xxxx clause will be used in the Create Table statement.
• Type Mapping. This option allows you to specify how data types are mapping between the source database and the target database. Specify this in the following format: from_type->to_type,from_type->to_type.

  Example: timestamp->date,time->char(8).

  In this example timestamp columns in the source table will be created as date columns in the target, and time columns will be created as char(8). If the length is omitted from the char specification, it will be taken from the length of the source column.

• # rows to scan. This specifies the number of records in the source file / Excel worksheet to scan to determine the data types of the columns. By default this is 1000 records for files or 100 rows for an Excel file. Specify 0 for AQT to parse the entire file.

Update Mode

The Create new table option cannot be specified when the load is running in Update mode.