Bulk Loads with the LOAD TABLE Statement

The LOAD TABLE statement efficiently imports data from a text or binary file into an existing database table, into column indexes created automatically or defined by users.

Set the permissions needed to execute a LOAD TABLE statement at the server command line, using the -gl option. We recommend the -gl all setting, which is the default set by start_iq. If -gl all is set, you must be the owner of the table, have ALTER or LOAD permission on the table, or have the ALTER ANY TABLE, LOAD ANY TABLE, or ALTER ANY OBJECT system privilege, to use the LOAD TABLE statement. You must also have a write lock on the table.

To load large amounts of data, most users create command files.

Transaction Processing and LOAD TABLE

When you issue the LOAD TABLE statement for an IQ table, a savepoint occurs automatically before the data is loaded.

If the load completes successfully, SAP Sybase IQ releases the savepoint. If the load fails, the transaction rolls back to the savepoint. This approach gives you flexibility in committing transactions. For example, if you issue two LOAD TABLE commands, you can ensure that either both commands commit or neither commits.

When you issue LOAD TABLE for a catalog store table, there is no automatic savepoint. If the load succeeds, it commits automatically. If the load fails, it rolls back. You cannot roll back a successful load of a catalog store table.

Load from a Flat File: UNIX Example

This example assumes that no explicit data conversion is needed, and that the width of input columns matches the width of columns in the Departments table. The flat file dept.txt must exist at the specified location.

The statement loads the data from the file dept.txt into all columns of the departmenttable.

LOAD TABLE Departments
( DepartmentID, DepartmentName, DepartmentHeadID )
FROM '/d1/MILL1/dept.txt'

File Specification Requirements for Loads

In the FROM clause, use filename-string to specify files, and use commas to separate multiple strings.

The files are read one at a time, and processed in a left-to-right order as specified in the FROM clause. Any SKIP or LIMIT value only applies at the beginning of the load, not for each file.

If a load cannot complete, for example due to insufficient memory, the entire load transaction rolls back.

The filename-string is passed to the server as a string, which is subject to the same formatting requirements as other SQL strings. In particular:

If a backslash (\) precedes the characters n, x, or \, it is considered an escape character. For this reason, to indicate directory paths in Windows systems, you must represent the backslash character by two backslashes if the next character is any of those listed.
To load data from the file c:\newinput.dat into the employee table, use:
```
LOAD TABLE employees
FROM 'c:\\newinput.dat' ...
```
For server-side loading (LOAD TABLE... FROM or LOAD TABLE...USING FILE), the path name 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 name refers to directories on the server machine, not on the client machine. The input file for the load must be on the server machine.
For client-side data loading (LOAD TABLE ... USING CLIENT FILE), the path name must be relative to the client application. The directory name refers to directories on the client machine.

Loads That Specify Named Pipes

When you load from a named pipe on Windows, the program writing to the pipe must close the pipe in a special way. It must call FlushFileBuffers( ), then DisconnectNamedPipe( ). If the program does not do this, SAP Sybase IQ reports an exception from hos_io::Read( ). This issues a PIPE_NOT_CONNECTED error, which notifies SAP Sybase IQ that the pipe was shut down in an orderly manner rather than as an uncontrolled disconnect. Refer to the Microsoft documentation for details on these calls.