15 July, 2012

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

8 comments:

  1. جزاك الله خير يابشمهندس

    ReplyDelete
  2. exactly i got the same requirment... You saved my time alot ..

    Thank you..

    ReplyDelete
  3. Thanks great work

    ReplyDelete
  4. Hey Really nice work.!
    had modified your code to migrate all table in a schema into postgreSQL compliant database.!

    Hope you wont mind publish same in my blog with reference.
    SET SERVEROUTPUT ON;
    DECLARE
    IN_TABLE_NAME VARCHAR2(4000) := '%';
    IN_OWNER_NAME VARCHAR2(4000) := 'OWNER_NAME';
    IN_MAX_RECORDS INTEGER := 10;

    LC$COLS_SELECT VARCHAR2 (4000);
    LC$COLS_VALUES VARCHAR2 (4000);
    LC$COLOUMN VARCHAR2 (200);
    CURSOR LCUR$TAB_COLUMNS (IN_OWNER_NAME VARCHAR2 , IN_TABLE_NAME VARCHAR2)
    IS
    SELECT COLUMN_NAME, DATA_TYPE, COLUMN_ID
    FROM DBA_TAB_COLS
    WHERE TABLE_NAME LIKE IN_TABLE_NAME
    AND OWNER LIKE IN_OWNER_NAME
    AND DATA_TYPE NOT IN ('LONG','CLOB','RAW' , 'BLOB')
    ORDER BY COLUMN_ID;
    BEGIN
    FOR REC_TAB IN (SELECT DISTINCT TABLE_NAME , OWNER FROM DBA_TABLES WHERE OWNER LIKE IN_OWNER_NAME )
    LOOP
    FOR LREC$TAB_COLUMNS IN LCUR$TAB_COLUMNS ( UPPER (REC_TAB.OWNER) , UPPER (REC_TAB.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_TIMESTAMP(''''''||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;

    DBMS_OUTPUT.PUT_LINE('SELECT ''INSERT INTO '
    || IN_OWNER_NAME || '.' || REC_TAB.TABLE_NAME
    || ' ('
    || LC$COLS_SELECT
    || ') VALUES ('
    || LC$COLS_VALUES
    || ');'' FROM '
    || IN_OWNER_NAME || '.' || REC_TAB.TABLE_NAME
    || ' WHERE ROWNUM < ' || (IN_MAX_RECORDS + 1) || ';');

    LC$COLS_VALUES:= '';
    LC$COLS_SELECT:='';
    LC$COLOUMN:= '';
    END LOOP;
    END;

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...