SELECT INTO

Purpose

You can use this statement to store the query results to a variable or file.

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

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

• SELECT ... INTO var_list stores the result set to 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';