How to Generate INSERT Statements from a Query when using SQLcl (Oracle)

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.