Loading all Tables in Schema

You can load a number of tables in a single operation by using the Load all Tables in Schema window. This is invoked from the Database Explorer window by clicking on the Load toolbar button and selecting Load all tables ion Schema or clicking on Tools > Load all tables in Schema.

The data for the tables can come from either another schema / database, a set of files in a directory, or worksheets in an Excel file.

Specify the following:

• Load tables in schema. You specify here the schema and database containing the tables to be loaded.
• Load data from. You specify here where the tables are to be loaded from. Choices are:
  • Tables. You are loading from tables in another schema. This can be in the same or another database.
  • Files. You are loading from files in a directory
  • Excel File. You are loading from worksheets in an Excel file.
• Match Tables. This field is used to determine the name of the table or file being loaded from. This is described later.

Selecting the Tables to Load

• Ctrl+A will select all tables to be loaded. Tables which do not exist in the source will not be selected.
• Ctrl+N will select no tables
• Filter can be used to select a set of tables

Load from Table

When this option is used, you need to specify the schema and database of the tables being loaded from.

You can click on Show Tables to see the tables in the schema.

Load from File

A screenshot showing this is given at in Running the Load.

When this option is used, you need to specify:

• the directory containing the files to be loaded from
• the file type of the files (eg. csv, txt)
• the delimiter used to delimit fields in the file
• whether the first line in the file is a header line with column names

When loading from a set of files, all files must be formatted in the same way. For instance, all files must use the same delimiter and have the same header line property.

You can click on Show Files to see all the files in the directory of the specified file type.

You can click on a line in the grid and click Show selected file. This will show you the contents of the file

Load from Excel File

When this option is used you need to specify:

• the Excel file
• whether the first line in the worksheets is a header line with column names

You can click on Show Worksheets to see all the worksheets in the Excel file.

Match tables

The Match tables field is used to "match" the target tables (tables being loaded) with the source tables/files (those being loaded from).

If the names of your target tables and source tables are the same, then use a Match tables of <name> (this is the default). Then, when AQT is loading table SALES it looks for a table called SALES in the source schema.

However, in many cases these might be different. For instance, the tables in the source schema might all be prefixed with "DEV_". In this case you want SALES to be loaded from DEV_SALES. In this case specify a Match tables of DEV_<name>.

When you specify a value for Match tables, the Load from Table/File column in the grid will be reloaded, and you will get a message as to whether the table or file exists in the source. You can use the Show Tables or Show Files button to see names of the tables/files in the source.

The syntax for specifying Match tables is the same used in the Data Loader mapping.

Load Options

The Load all Tables in Schema function uses the Data Loader. On the Options tab you can specify the options used for the load. These options are the same as those described in the Data Loader.

Running Load Mulitple in Batch

Load all Tables in Schema can be run in batch (unattended mode).

To create a script statement to run the load, use File > View Load Script. Note that the script statement will include the tables you have selected to load, so ensure that this is selected correctly before clicking on View Load Script.

The script statement can be run from either the Run SQL window or included as part of a batch script.