SELECT INTO

Description

This statement stores the query result in a variable or a file. The statements are described as follows:

• The SELECT ... INTO OUTFILE statement stores the result set in an external file and allows you to specify the output format.

• The SELECT ... INTO DUMPFILE statement writes a single, unformatted line to an external file.

• The SELECT ... INTO var_list statement stores the result set in variables.

Syntax

select_stmt INTO
    OUTFILE string [ {CHARSET | CHARACTER SET} charset_name] [field_opt] [line_opt]
  | DUMPFILE string
  | into_var_list

field_opt:
   {COLUMNS | FIELDS} field_term_list

line_opt:
    LINES line_term_list

field_term_list:
  field_term [, field_term ...]

field_term:
  {[OPTIONALLY] ENCLOSED | TERMINATED | ESCAPED} BY string

line_term_list:
  line_term [line_term]

line_term:
  {STARTING | TERMINATED} BY string

Parameters

Examples

• Export the query result set as a CSV file.

  obclient> SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;
  

• Export the full outer join result of tables A and B to an external file.

  obclient> SELECT distinct * FROM A FULL JOIN B USING(ID) INTO OUTFILE 'test.sql'
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n';