Import

Data from non-database sources that are contained in text files can be loaded into a database table by using the IMPORT statement as shown in the syntax specification below.

import_stmt:
          IMPORT INTO table_name FROM [CHAR | WCHAR | XML] file "filename"
               [AUTOCOMMIT [EVERY num [ROWS]]]

The data must either be stored in a comma-delimited or XML format. A comma-delimited format requires that each column value be specified in the order in which the columns are declared in the table. Absence of a column value is indicated by a blank or empty entry (e.g., ",,"). Specify WCHAR if the text is stored with wide-characters.

The AUTOCOMMIT option can be used to break up what would be a large transaction into multiple smaller transactions that will automatically issue a transaction commit after every num rows have been imported into table_name. This option will improve the system performance.

The following statements are used to load the sample data contained in comma-delimited text files into bookshop example database.

import into author from file "authors.txt";
import into book from file "books.txt";
import into genres from file "genres.txt";
import into subjects from file "subjects.txt";
import into related_name from file "names.txt";
import into genres_books from file "bookgens.txt";
import into subjects_books from file "booksubs.txt";
import into acctmgr from file "acctmgrs.txt";
import into patron from file "patrons.txt";
import into note from file "bnotes.txt";
import into note_line from file "bnotelines.txt";
import into note from file "pnotes.txt";
import into note_line from file "pnotelines.txt";
import into sale from file "sales.txt";
import into auction from file "auctions.txt";

In XML format the column values are identified using XML attributes or tags to identify the column name with which the tagged value is associated. The columns can be in any order but all necessary columns must be included (i.e., columns declared as not null without a default value or which are declared as a primary or unique key). Each row is bracketed between pairs of <ROW> and </ROW> tags. For each row column values are specified between pairs of <column_name> and </column_name> tags. The file begins with a <RAIMA-SQL> tag and ends with a </RAIMA-SQL> tag. A portion of file sponsors.xml which can be used to load the sponsor table in the nsfawards database is shown below.

<RAIMA-SQL>
 	...
    <ROW>
        <name>UNAVCO, Inc.</name>
        <addr>3360 Mitchell Lane</addr>
        <city>Boulder</city>
        <state>CO</state>
        <zip>80301</zip>
    </ROW>
    <ROW>
        <name>UNIAX Corporation</name>
        <addr>6780 Cortona Drive</addr>
        <city>Santa Barbara</city>
        <state>CA</state>
        <zip>93117</zip>
    </ROW>
    <ROW>
        <name>UNIVERSITY OF MICHIGAN</name>
        <addr>2455 Hayward Street</addr>
        <city>Ann Arbor</city>
        <state>MI</state>
        <zip>48109</zip>
    </ROW>
    <ROW>
        <name>UNIVERSITY OF WISCONSIN MA</name>
        <addr></addr>
        <city></city>
        <state>  </state>
        <zip>  /  </zip>
    </ROW>
    <ROW>
        <name>UNT Hlth Sci Ctr at Fort W</name>
        <addr>Camp Bowie at Montgomery</addr>
        <city>Fort Worth</city>
        <state>TX</state>
        <zip>76107</zip>
    </ROW>
    <ROW>
        <name>URS Group, Inc.</name>
        <addr>566 El Dorado Street - 2nd Floor</addr>
        <city>Pasadena</city>
        <state>CA</state>
        <zip>91101</zip>
    </ROW>
    <ROW>
        <name>US Army Corps of Engineers</name>
        <addr>Transatlantic Programs Center</addr>
        <city>Winchester</city>
        <state>VA</state>
        <zip>22601</zip>
    </ROW>
 	...
</RAIMA-SQL>

The following statement loads the sponsor table in the nsfawards database from the above file.

import into sponsor from xml file "sponsors.xml";