LOAD - Loading Data
You load definitions and/or data into a Mimer SQL database using the
LOADcommand has the following syntax:
MIMLOAD, or with any ODBC-based SQL interpreter.
For information on
MIMLOAD, see MIMLOAD - Data Load and Unload.
LOADcommand copies definitions and/or data from one or more files. When loading information from more than one file, the files are read in the order defined. The input file(s) are expected to form a valid sequence of definitions, data descriptions and data.
Triggers defined against the affected tables are applied when the data is loaded.
When a file contains data for more than one table, the data for each table must be contained in a section that is introduced by a data description header. For more information, see Data Description Headers and Files.
If the data in the file does not have a data description header, there must be a data description file that contains the header information.
This means that the file can only contain data from one table. Data description files and data files can of course be concatenated into a single file containing data for several tables.
A definition file contains definition statements to create objects. A definition file can, for example, be divided into two files where one file is place first in the file list; i.e. executed before any data is loaded, and the other file is placed at the end of the file list, i.e. executed after the data is loaded.
An example file sequence can be as follows: first in the file list, a file that contains object definitions; second, a file that describes the data to be loaded (the information in this file is equal to the corresponding information that can be given as a header in the data file); third, the data file; and fourth, a second definition file including referential constraints and triggers.
You can specify the name of the table into which the information shall be loaded in the data file header(s) or the data description file. The default is the table name from which the data was unloaded.
LOADscans a file, it detects if a field uses a text qualifier by checking if the first character in the field is a text qualifier. If a text qualifier character is found in the field data, the character is doubled, i.e if the text qualifier is a double quote, the data
ab'cis equal to the data
The STDIN Option
When you use the
STDINoption, input is read from the standard input stream. See Using STDIN/STDOUT/STDERR.
The AS Option
By using the
ASoption together with a file name specification, you can specify the character set used by the file to be loaded.
The character sets available are:
UTFxxformat with big or little endian byte order.
UTFxxwithout endian notion means that the common endian for the current platform is assumed.
The default character set used, if you do not use the
ASoption, is the default used in your host operating system.
For more information, see File Format Specifications.
You specify the log file using
LOG. This log file will include warnings and progress information about the load operation.
The STDERR Option
When you specify
LOG STDERR, informational messages are written to the standard error stream. See Using STDIN/STDOUT/STDERR.
The WITH SHARED ACCESS Option
LOAD's default behavior implies that you have exclusive access to the databank being loaded with data. If you need shared access, you can use the
WITH SHARED ACCESSoption. In most cases, this will lead to a slower data load using row-wise insert.
LOADuses a fast data load facility to increase performance. The alternative is to insert data row-wise as if using an SQL
LOADuses the fast data load facility in most cases, but there are some situations that need row-wise insertion due to certain referential constraints. In such cases, a warning message will tell you that fast data load cannot be used and the operation will continue using row-wise insertions.
When row-wise insertions are performed, loads are recorded in
LOGDB(assuming the databank is defined with the
The START AT Option
You can use the
START AToption to restart a failed load operation.
START ATvalue can be set to a line where a data definition statement is located or a data descriptor header starts (
The USING Option
USINGoption enables you to use an SQL statement to specify the information to be loaded and the target for the information.
The SQL statements you can use are:
CALLto a procedure with input parameter markers only.
The following example is a straightforward import of the input file, using default options:LOAD FROM 'table_t.data' LOG 'table_t.log';
The following example imports a data file, preceded by a data description file, using the default options:LOAD FROM 'table_t.desc', 'table_t.data' LOG 'table_t.log';
The following example imports the first four columns of data in the file to the table named
detailsfrom a file in
UTF16format:LOAD FROM 'table_t.data' AS UTF16 LOG 'table_t_dataload.log' USING INSERT INTO details VALUES (?,?,?,?);
The following example uses an
UPDATEstatement where the first column
C1and the second column
C2of the data input file are used:LOAD FROM 'table_t.data' AS UTF16 LOG 'table_t_dataload.log' USING UPDATE details SET c1=? WHERE c2=?;
The following example uses a
DELETEstatement where the input data is used to qualify records to delete:LOAD FROM 'table_t.data' AS UTF16 LOG 'table_t_dataload.log' USING DELETE FROM details WHERE c2=? AND c3=?;
Mimer Information Technology AB
Phone: +46 18 780 92 00