Importing tables (SQL)

You can use SQL to import data from a text file, another table in any database, or a shape file, into a table in your database.

Prerequisites

You must have the CREATE TABLE privilege to create a table owned by you, or have the CREATE ANY TABLE or CREATE ANY OBJECT system privilege to create a table owned by others.

The privileges required to import (load) data depend on the settings of the -gl database option, as well as the source of the data you are importing from. See the LOAD TABLE statement for more information about the privileges required to load data.

Task

Use the CREATE TABLE statement to create the destination table. For example:

CREATE TABLE Departments (
DepartmentID          integer NOT NULL,
DepartmentName        char(40) NOT NULL,
DepartmentHeadID      integer NULL,
CONSTRAINT DepartmentsKey PRIMARY KEY (DepartmentID) );

Execute a LOAD TABLE statement. For example:

LOAD TABLE Departments
FROM 'C:\\ServerTemp\\Departments.csv';

To keep trailing blanks in your values, use the STRIP OFF clause in your LOAD TABLE statement. The default setting (STRIP RTRIM) strips trailing blanks from values before inserting them.

The LOAD TABLE statement adds the contents of the file to the existing rows of the table; it does not replace the existing rows in the table. You can use the TRUNCATE TABLE statement to remove all the rows from a table.

The FROM clause specifies a file on the database server computer.

Neither the TRUNCATE TABLE statement nor the LOAD TABLE statement fires triggers or perform referential integrity actions, such as cascaded deletes.

Results

The data is imported into the specified table.

Importing tables (SQL)

Task

See also