Exporting Data from a table in Oracle to Excel(csv) and then importing from Excel(csv) to another Oracle table

Of late I had to export data from few tables from one database to another. Since the records were only in backend and not through PeopleSoft usage of Data Mover is ruled out. DB Link creation was ruled out due to lack of privileges for the given user id I connected. And even did not have third party tools like TOAD which allow exporting data from table in the format of INSERT scripts, .csv, .txt etc and I was plainly left with SQL Plus. Googling around came across few ways of importing data from a table to excel.

Method I - Exporting data to Excel Sheet
SQL> set feed off markup html on spool on
SQL> spool c:\tblname_dbname.xls
SQL> select * from tblname;
SQL> spool off
SQL> set markup html off spool off

This creates a lovely spreadsheet with data for the corresponding SQL query. However it requires more work in excel spreadsheet to use this data for inserting it in another table.

Method II - Exporting data to CSV:
SELECT field1||','||field2||','||field3','||field4
FROM tablename
WHERE <conditions> ;

Importing data from CSV to Oracle:

1. Open the CSV file and add a new column at the beginning. Have your insert statement in the first(new) column as: INSERT INTO tablename field1, field2,... VALUES(
2. In the last column add this );
3. Fill both these values till the end of respective columns.
4. Save this CSV file with a .txt extension
5. From SQL Plus, execute the below SQL:

SQL&gt; spool on

SQL&gt; @c:\tblname_dbname.txt

SQL&gt; spool off

Review for any error messages before issuing a COMMIT statement.

This is not the only way for importing/exporting data in a Oracle table with excel or csv. For there isn't one defined way of doing this and there are multiple options available to export data from one oracle table to another. However for faster processing, SQL Loader is the best option.