Create Insert Statement for Table Data

In every site we have more than one environment (Testing, Development, Production, .....etc).
Sometimes we insert any data in one environment and want to migrate it to another environment.

Usually we use database editors to do this task like (Toad, Plsql Developer, SQL developer, .... etc), but in my post today I will create PLSQL function that will generate insert statement for you.

Here is the GEN_INSERT_STATEMENT function is used return SQL select  statement against input table parameter which we can use it to generate insert statement

 CREATE OR REPLACE FUNCTION GEN_INSERT_STATEMENT (IN_TABLE_NAME VARCHAR2)  
   RETURN VARCHAR2  
 IS  
   LC$COLS_SELECT     VARCHAR2 (4000);  
   LC$COLS_VALUES     VARCHAR2 (4000);  
   LC$COLOUMN      VARCHAR2 (200);  
   CURSOR LCUR$TAB_COLUMNS (IN_TABLE_NAME VARCHAR2)  
   IS  
     SELECT COLUMN_NAME, DATA_TYPE, COLUMN_ID  
      FROM USER_TAB_COLS  
      WHERE TABLE_NAME = IN_TABLE_NAME  
    ORDER BY COLUMN_ID;  
 BEGIN  
   FOR LREC$TAB_COLUMNS IN LCUR$TAB_COLUMNS (UPPER (IN_TABLE_NAME))  
   LOOP  
    LC$COLS_SELECT :=  
       LC$COLS_SELECT  
      || CASE LREC$TAB_COLUMNS.COLUMN_ID WHEN 1 THEN '' ELSE ',' END  
      || LREC$TAB_COLUMNS.COLUMN_NAME;  
    IF INSTR (LREC$TAB_COLUMNS.DATA_TYPE, 'CHAR') > 0  
    THEN  
      LC$COLOUMN :=  
       '''''''''||' || LREC$TAB_COLUMNS.COLUMN_NAME || '||''''''''';  
    ELSIF INSTR (LREC$TAB_COLUMNS.DATA_TYPE, 'DATE') > 0  
    THEN  
      LC$COLOUMN :=  
       '''TO_DATE(''''''||TO_CHAR(' || LREC$TAB_COLUMNS.COLUMN_NAME  
       || ',''mm/dd/yyyy hh24:mi'')||'''''',''''mm/dd/yyyy hh24:mi'''')''';  
    ELSE  
      LC$COLOUMN := LREC$TAB_COLUMNS.COLUMN_NAME;  
    END IF;  
    LC$COLS_VALUES :=  
       LC$COLS_VALUES  
      || CASE LREC$TAB_COLUMNS.COLUMN_ID WHEN 1 THEN '' ELSE ',' END  
      || '''||DECODE('  
      || LREC$TAB_COLUMNS.COLUMN_NAME  
      || ',NULL,''NULL'','  
      || LC$COLOUMN  
      || ')||''';  
   END LOOP;  
   RETURN  'SELECT ''INSERT INTO '  
      || IN_TABLE_NAME  
      || ' ('  
      || LC$COLS_SELECT  
      || ') VALUES ('  
      || LC$COLS_VALUES  
      || ');'' FROM '  
      || IN_TABLE_NAME  
      || ';';  
 END;  

Let's Now run function for table EMP

 SELECT GEN_INSERT_STATEMENT('EMP') FROM DUAL;  

The output is select statement that we should run it to get insert statement of data.

 SELECT 'INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES ('  
     || DECODE (EMPNO, NULL, 'NULL', EMPNO)  
     || ','  
     || DECODE (ENAME, NULL, 'NULL', '''' || ENAME || '''')  
     || ','  
     || DECODE (JOB, NULL, 'NULL', '''' || JOB || '''')  
     || ','  
     || DECODE (MGR, NULL, 'NULL', MGR)  
     || ','  
     || DECODE (  
        HIREDATE,  
        NULL, 'NULL',  
         'TO_DATE('''  
        || TO_CHAR (HIREDATE, 'mm/dd/yyyy hh24:mi')  
        || ''',''mm/dd/yyyy hh24:mi'')')  
     || ','  
     || DECODE (SAL, NULL, 'NULL', SAL)  
     || ','  
     || DECODE (COMM, NULL, 'NULL', COMM)  
     || ','  
     || DECODE (DEPTNO, NULL, 'NULL', DEPTNO)  
     || ');'  
  FROM EMP;  

If we run the previous select statement it will generate inert statement for data existed in table EMP.

 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7369,'SMITH','CLERK',7902,TO_DATE('12/17/1980 00:00','mm/dd/yyyy hh24:mi'),800,NULL,20);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('02/20/1981 00:00','mm/dd/yyyy hh24:mi'),1600,300,30);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('02/22/1981 00:00','mm/dd/yyyy hh24:mi'),1250,500,30);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7566,'JONES','MANAGER',7839,TO_DATE('04/02/1981 00:00','mm/dd/yyyy hh24:mi'),2975,NULL,20);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('09/28/1981 00:00','mm/dd/yyyy hh24:mi'),1250,1400,30);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('05/01/1981 00:00','mm/dd/yyyy hh24:mi'),2850,NULL,30);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('06/09/1981 00:00','mm/dd/yyyy hh24:mi'),2450,NULL,10);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('04/19/1987 00:00','mm/dd/yyyy hh24:mi'),3000,NULL,20);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('11/17/1981 00:00','mm/dd/yyyy hh24:mi'),5000,NULL,10);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('09/08/1981 00:00','mm/dd/yyyy hh24:mi'),1500,0,30);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('05/23/1987 00:00','mm/dd/yyyy hh24:mi'),1100,NULL,20);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7900,'JAMES','CLERK',7698,TO_DATE('12/03/1981 00:00','mm/dd/yyyy hh24:mi'),950,NULL,30);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7902,'FORD','ANALYST',7566,TO_DATE('12/03/1981 00:00','mm/dd/yyyy hh24:mi'),3000,NULL,20);  
 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7934,'MILLER','CLERK',7782,TO_DATE('01/23/1982 00:00','mm/dd/yyyy hh24:mi'),1300,NULL,10);  

Thanks