SELECT INTO

Purpose

You can use this statement to store the query results in a variable or file. The parameters are described as follows:

• SELECT ... INTO OUTFILE stores the result set in an external file in the specified output format.

  Note

  When you use the SELECT ... INTO OUTFILE statement to export data, you can use \N to represent NULL.

• SELECT ... INTO DUMPFILE writes a single line without any format to an external file.

• SELECT ... INTO var_list 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 results 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 join results 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';