SQL File Manipulation

This page describes various methods for extracting data from a database into a plain text format (e.g. CSV, JSON, XML etc) ingesting data into a database from a plain text format and manipulating data in a plain text file from within the database. The Database technologies where each method applies are shown by the buttons in the right hand column.

Extract

MySQL

INTO OUTFILE

Tab separated, newline terminated...

SELECT mycol1,mycol2,mycol3

  FROM mytable

  INTO OUTFILE '/tmp/myfile.txt';

Comma separated, newline terminated, each field enclosed in double quotes...

SELECT mycol1,mycol2,mycol3

  FROM mytable

  INTO OUTFILE '/tmp/myfile.csv'

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

Note: You cannot overwrite an existing file using "INTO OUTFILE". Delete the file before running the SQL or ensure the SQL generates a unique outfile name. If you write to an existing file you will see:ERROR 1086 (HY000): File '...' already exists.
Note: If secure_file_priv is set then you can only write to the location returned by:SHOW VARIABLES LIKE "secure_file_priv";If you try to write elsewhere you will see: ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Oracle

SPOOL

SET MARKUP csv ON DELIMITER ,

SPOOL myfile.csv


SELECT mycol1,mycol2,mycol3

  FROM mytable;


SPOOL OFF

Note: This syntax works in 12.2 and above. For older versions use the alternate syntax shown below...

SET COLSEP ,

SET PAGESIZE 0

SET TRIMSPOOL ON

SET HEADSEP OFF

SET LINESIZE 80

SET NUMW 10

SPOOL myfile.csv


SELECT mycol1,mycol2,mycol3

  FROM mytable;


SPOOL OFF

UTL_FILE

Microsoft SQL Server

PostgreSQL

In psql...

SELECT mycol1,mycol2,mycol3

FROM mytable \g /tmp/myfile.txt

To append to an existing file...

SELECT mycol1,mycol2,mycol3

FROM mytable \g | cat >>/tmp/myfile.txt

Alternate method in psql...

\o /tmp/myfile.txt

SELECT mycol1,mycol2,mycol3

FROM mytable;

\o

From shell...

echo 'SELECT mycol1,mycol2,mycol3 FROM mytable' | psql myDB >> /tmp/myfile.txt

Load

MySQL

Oracle

SQL*Loader

UTL_FILE

SQL Developer

Microsoft SQL Server

PostgreSQL

Transform

MySQL

Oracle

Microsoft SQL Server

PostgreSQL

Bibliography