Chapter 9 SQL Statements
561
You can use LOAD TABLE on a global temporary table, but the temporary
table must have been created with the ON COMMIT PRESERVE ROWS
clause, because LOAD TABLE does a COMMIT after the load. LOAD
TABLE cannot be used on declared temporary tables.
If the ASCII file has entries such that a column appears to be NULL, LOAD
TABLE treats it as null. If the column in that position cannot be NULL, it
inserts a zero in numeric columns and an empty string in character columns.
LOAD TABLE skips empty lines in the input file.
Any columns not present in the column list become NULL if the
DEFAULTS option is off. If DEFAULTS is on and the column has a default
value, that value will be used. If DEFAULTS is off, and a non-nullable
column is omitted from the column list, the engine attempts to convert the
empty string to the column’s type.
Caution
LOAD TABLE is intended solely for fast loading of large amounts of data.
It is not intended for routine use in applications.
FROM option The filename-string is passed to the server as a string. The
string is therefore subject to the same formatting requirements as other SQL
strings. In particular:
♦ To indicate directory paths, the backslash character \ must be
represented by two backslashes. The statement to load data from the file
c:\temp\input.dat
into the employee table is:
LOAD TABLE employee
FROM ’c:\\temp\\input.dat’ ...
♦ The pathname is relative to the database server, not to the client
application. If you are running the statement on a database server on
another computer, the directory names refers to directories on the server
machine, not on the client machine.
♦ You can use UNC path names to load data from files on computers other
than the server. For example, on a Windows for Workgroups,
Windows 95, or Windows NT network, you may use the following
statement to load data from a file on the client machine:
LOAD TABLE employee
FROM ’\\\\client\\temp\\input.dat’
CHECK CONSTRAINTS option This option is ON by default, but the
Unload utility writes out LOAD TABLE statements with the option set to
OFF.
Parameters
To Next Page
Loading...