Amending, Exporting & Loading Data
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.
Running the Load
To run the Load, click on the Load button.
click on Abort to terminate the current load
click on Pause to stop the load after the current load completes
As the load runs you will be shown the progress of the load. In this:
Read - is the number of rows/lines read from the source
Loaded - is the number of rows succesfully loaded into the table
Dup - is the number of rows not loaded due to a Duplicate Row error
Error - is the number of rows not loaded due to another error.
Once the Load has completed
Click on Show Report File to view the report file, which has a summary of the load.
If a load has failed, click on the line in the grid. Another panel will be displayed. You will have options:
Show Error File - this will show you the errors the load encountered
Show Error Rows - this will show you the rows that were not loaded due to an error
Show Dup Rows - this will show you the rows that were not loaded due to a duplicate row error
You can use the Reset button to return the window to it's original state to rerun the load.
Table Selection
As you run the load, the tables are de-selected once they have been loaded. This is so that they will not be loaded a second time if you resume the load after it has been paused, or if you click on Load a second time.
Once the load has completed, all tables will be de-selected. If you wish to run the load a second time, click on Select > Select Tables used in last load (or Ctrl+L).
The Reset button will also re-select all the tables which were originally selected.
Create Table mode
Load all Tables in Schema has a Create Table mode. When this is specified, the tables being loaded are created from the source. This provides a very fast method for creating and loading a large number of tables.
Notes
table-creation will be basic. Primary keys and indexes will not be included. To create tables more completely you should generate a DDL script and create the tables using this.
for DB2 and Oracle users, before running the load, go to the Options tab and ensure that the correct Tablespace has been selected.
when data is being loaded from Files and Excel Files, AQT will parse the files to determine the data types of the fields in the file.
when data is being loaded from Files and Excel Files, and the First row has column names option is specified, AQT take the column names from this header row.
Match tables is used to construct the name of the Table (being loaded) from the Load from Table. Note that this is opposite to way it normally works - which is for the Load from Table name to be constructed from Table (being loaded).