CODEWITHSUNDEEP
sqloracle-database
kahsay kalayu
kahsay kalayu

Reputation: 1

sql query to export data from different tables to file

Currently, am trying to export data from different databases(oracle, sqlserver, MySQL...etc) to file using sql statment. can some one help me to do so, just like below:

SELECT order_id,product_name FROM orders INTO OUTFILE 'orders.csv' 
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

But my query doesn't work,with the following error 

ORA-00933: SQL command not properly ended

Upvotes: 0

Views: 975

Answers (2)

Littlefoot
Littlefoot

Reputation: 143083

As of Oracle, a simple option is to spool the result of a query into a file (which is something you tried to do in your example). It is done from SQL*Plus, a command line tool. You're supposed to learn about SET commands which allow you to make a pretty output.

Here's an example of such commands:

SQL> set termout off
SQL> set trimspool on
SQL> set echo off
SQL> set verify off
SQL> set autoprint off
SQL> set serveroutput off
SQL> set arraysize 1000
SQL> set pagesize 0
SQL> set linesize 100
SQL> set long 10000
SQL> set numwidth 10
SQL> set feedback off
SQL> set colsep ';'
SQL> col empno format 99999
SQL> col ename format a10
SQL> col sal format 999G990
SQL> spool emps.txt
SQL> select empno, ename, sal from emp;
  7369;SMITH     ;     800
  7499;ALLEN     ;   1.600
  7521;WARD      ;   1.250
  7566;JONES     ;   2.975
  7654;MARTIN    ;   1.250
  7698;BLAKE     ;   2.850
  7782;CLARK     ;   2.450
  7839;KING      ;   5.000
  7844;TURNER    ;   1.500
  7900;JAMES     ;     950
  7902;FORD      ;   3.000
  7934;MILLER    ;   1.300
  7788;SCOTT     ;   3.000
  7876;ADAMS     ;   1.100
SQL> spool off
SQL>

By the way, lines 2-4 you wrote look like SQL*Loader's control file which is used to load data (not to unload it).

Upvotes: 2

koolkats99
koolkats99

Reputation: 41

Ultimately, this depends on which database you're using.

The easiest way to do this would be using a database manager. For example, I know that phpMyAdmin has an export function and is easy to do that with (it's very good, I'll link a post which can help you with this). MySQL Workbench has one as well.

You will need different syntax for each different database. I'll try to provide answers for a few of the possibilities:

MySQL: You would use the command line, navigate to a folder where you want the output file to be and then enter the command:

mysqldump --add-drop-table -u admin -p`cat /etc/psa/.psa.shadow` dbname > dbname.sql

Note that this will export the entire DB which might not be what you want. See a nice guide for this here. If you'd like something a bit more field selective (using SELECT), give this a shot:

SELECT order_id,product_name INTO outfile 'full_path/orders.csv' fields terminated BY ',' from orders;

Oracle: You should be able to use the following command:

exp username/password PARAMETER=(value1,value2,...,valuen)

This is a more complicated one, I'd recommend checking out their official documentation for EXPORTING here.

MS SQL: This is probably the best one. Your best bet is to go with server management studio as it's extremely simple there, as I don't recall and could not find any easy commands for doing this. You might be able to do something with the bcp command (in cmd).

The Truth: Use a server management studio, this would by far be your best bet. If for some reason, you need to do it by command prompt or by script, it's possible but not really meant for that. Check this out for general help as well.

Upvotes: 0

Related Questions