ORACLE: Build 'INSERT INTO' statements from SELECT * results

Question

Beginner in PL/SQL here. I am trying to build a script that takes the results from a table "ADMIN.ACCT_HRS" and creates INSERT INTO statements for each row, so that I could feasibly rebuild the table on another server. The search parameter for ACCT_HRS is the "WID", which I am pulling from the "ADMIN.INVENTORY" table using a user-inputted value, "pknum". This script is still in its infancy so I am only targeting one table; but eventually, I plan to adapt it to generate the INSERT INTO statements for six different tables with only one user-inputted value, all with constantly changing numbers of rows and columns. So it must be dynamic.

So far, my script looks like this:

DECLARE
-- declare variables for pkNum input and for SQL statement output
v_pkNum VARCHAR2(16);
v_sqlBuild VARCHAR2(4000);
v_sqlOutput VARCHAR2(4000);

BEGIN
-- prompt for pkNum value
DBMS_OUTPUT.PUT_LINE('pkNum:');
v_pkNum := '&pkNum';

-- initialize the output statement
v_sqlBuild := 'INSERT INTO ACCT_HRS (';

-- create a list that stores the columns from table 
FOR col IN (SELECT column_name FROM all_tab_columns WHERE table_name = 'ACCT_HRS' AND owner = 'ADMIN') LOOP       
    v_sqlBuild := v_sqlBuild || col.column_name || ', ';
END LOOP;

-- trim the trailing comma and add VALUES keyword
v_sqlBuild := RTRIM(v_sqlBuild, ', ') || ') VALUES (';


/* generate INSERT INTO statement*/
-- loop to iterate through rows in table
FOR row IN (SELECT * FROM ADMIN.ACCT_HRS WHERE WID IN (SELECT WID FROM ADMIN.INVENTORY WHERE pkNum = v_pkNum)) LOOP

    -- Reset the value part of the SQL statement
    v_sqlOutput := v_sqlBuild;
    
    -- loop to iterate through each cell in row
    FOR cell IN (SELECT column_name FROM all_tab_columns WHERE table_name = 'ACCT_HRS' AND owner = 'ADMIN') LOOP
        v_sqlOutput := v_sqlOutput || cell[row.column_name] || ', ';
    END LOOP;

    -- trim the trailing comma and add closing parenthesis
    v_sqlOutput := RTRIM(v_sqlOutput, ', ') || ');';

    -- print the INSERT INTO statement
    DBMS_OUTPUT.PUT_LINE(v_sqlOutput);

END LOOP;

END;
/

Answer 1

Can I make an alternative suggestion, because it will avoid re-inventing the wheel. If you use the free client SQLcl, it can handle this trivially, eg

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> set sqlformat insert

SQL> select * from dept;

REM INSERTING into DEPT
SET DEFINE OFF;
Insert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');

or just do it directly in a given SQL

SQL> select /*insert*/ * from dept;
REM INSERTING into DEPT
SET DEFINE OFF;
Insert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');

But if you do want to build it yourself, here's a video showing various techniques on how to do handle more datatypes and create scripts that will run a lot faster as well. It also covers SQLcl

https://youtu.be/LdGtl09C6DM