UNLOAD statement

Use the UNLOAD statement to write the rows retrieved by a SELECT statement to an operating-system file. The UNLOAD statement is an extension to the ANSI/ISO standard for SQL.

Syntax

Only DB-Access supports the UNLOAD statement.

Read syntax diagramSkip visual syntax diagram
>>-UNLOAD TO--'filename'--+------------------------+------------>
                          '-DELIMITER--'delimiter'-'   

                          (1)     
>--+-| SELECT Statement |-----+--------------------------------><
   '-variable-----------------'   

Notes:
  1. See SELECT statement
Element Description Restrictions Syntax
delimiter Quoted string to specify the field delimiter character in filename file See DELIMITER Clause Quoted String
filename Operating-system file to receive the rows. Default pathname is the current directory. See UNLOAD TO File. Quoted String
variable Host variable that contains the text of a valid SELECT statement Must have been declared as a character data type Language- specific

Usage

Important: Use the UNLOAD statement only with DB-Access.

The UNLOAD statement copies to a file the rows retrieved by a query. You must have the Select privilege on all columns specified in the SELECT statement. For information on database-level and table-level privileges, see GRANT statement.

You can specify a literal SELECT statement or a character variable that contains the text of a SELECT statement. (See SELECT statement.)

The following example unloads rows whose value of customer.customer_num is greater than or equal to 138, and writes them to a file named cust_file:
UNLOAD TO 'cust_file' DELIMITER '!' 
   SELECT * FROM customer WHERE customer_num> = 138;
The resulting output file, cust_file, contains two rows of data values:
138!Jeffery!Padgett!Wheel Thrills!3450 El Camino!Suite 10!Palo Alto!CA!94306!!
139!Linda!Lane!Palo Alto Bicycles!2344 University!!Palo Alto!CA!94301!
    (415)323-5400

Copyright© 2020 HCL Technologies Limited