SQL Reference - TERMINATED

TERMINATED

TERMINATED in SQL is a clause used during the import or export of data, most commonly in a LOAD DATA INFILE statement or SELECT...INTO OUTFILE statement. It specifies the delineation characters, allowing data to be separated by defined characters such as a comma, tab, etc. This allows for easier parsing of the file or data stream, enabling structured data transfer between SQL and files.

Example

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE tbl1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Output

The command above won’t display any output on the terminal, it will just load the data from a file into your tbl1 table.

Explanation

In SQL, the TERMINATED BY clause within the LOAD DATA INFILE command is used for identifying how the data set is split into multiple fields and lines. This example reads in a CSV file from the specified path and inserts the data into the table tbl1. Here, FIELDS TERMINATED BY ',' specifies that fields in each row are separated by a comma, and LINES TERMINATED BY '\n' specifies that a newline character indicates a new row.

Example

CREATE TABLE courses
    (course_id NUMBER(10),
    title VARCHAR2(50) TERMINATED BY ';',
    duration NUMBER(10))
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
    DEFAULT DIRECTORY sample_dir
    LOCATION ('sample_file.csv'));

Output

Table COURSES created.

Explanation

In the example above, TERMINATED BY is used in the “create table” statement for the external table courses. The title field in each record is terminated by the semicolon (;) in the sample_file.csv located in the sample_dir. The tool reads and interprets the file record until it sees the semicolon (;), at which point it understands this marks the end of the title field. After table creation, the response “Table COURSES created” will be displayed, indicating the operation was successful.