When using SQLcl with Oracle Database, you can set SQLFORMAT
to insert
in order to output the query results as INSERT
statements.
Example
Here’s an example to demonstrate:
SET SQLFORMAT insert;
SELECT * FROM regions;
Result:
REM INSERTING into REGIONS SET DEFINE OFF; Insert into REGIONS (REGION_ID,REGION_NAME) values (1,'Europe'); Insert into REGIONS (REGION_ID,REGION_NAME) values (2,'Americas'); Insert into REGIONS (REGION_ID,REGION_NAME) values (3,'Asia'); Insert into REGIONS (REGION_ID,REGION_NAME) values (4,'Middle East and Africa'); 4 rows selected.
Just to be clear, here are the query results when using ansiconsole
:
SET SQLFORMAT ansiconsole;
SELECT * FROM regions;
Result:
REGION_ID REGION_NAME ____________ _________________________ 1 Europe 2 Americas 3 Asia 4 Middle East and Africa
Export to a File
If you need to store the INSERT
statements in a file, you can use the SPOOL
command to export the results to a file.
SET SQLFORMAT insert;
SPOOL '/Users/barney/data/insert_regions.sql';
SELECT * FROM regions;
SPOOL off;
SET SQLFORMAT ansiconsole;
That exported the result to a file called insert_regions.sql
at the specified location.
Once the query was exported to a file, I set SPOOL
to off
and SQLFORMAT
to ansiconcole
.
Here’s what the resulting file looks like:
REM INSERTING into REGIONS SET DEFINE OFF; Insert into REGIONS (REGION_ID,REGION_NAME) values (1,'Europe'); Insert into REGIONS (REGION_ID,REGION_NAME) values (2,'Americas'); Insert into REGIONS (REGION_ID,REGION_NAME) values (3,'Asia'); Insert into REGIONS (REGION_ID,REGION_NAME) values (4,'Middle East and Africa'); 4 rows selected.
Remove Feedback
You can remove the X rows selected
with SET FEEDBACK off
:
SET SQLFORMAT insert;
SET FEEDBACK off;
SELECT * FROM regions;
SET FEEDBACK on;
SET SQLFORMAT ansiconsole;
Result:
REM INSERTING into REGIONS SET DEFINE OFF; Insert into REGIONS (REGION_ID,REGION_NAME) values (1,'Europe'); Insert into REGIONS (REGION_ID,REGION_NAME) values (2,'Americas'); Insert into REGIONS (REGION_ID,REGION_NAME) values (3,'Asia'); Insert into REGIONS (REGION_ID,REGION_NAME) values (4,'Middle East and Africa');
In this case I turned FEEDBACK
back on after exporting the file, and set SQLFORMAT
back to ansiconsole
.