Some time back, I have a requirement to pull the data on some server and load into my local Db. The best decoupled way to do that is create insert scripts on server and run them back in local(sure, there will be some key constraints and checks, which I am planning to disable and import data).
I googled for the solution, I just got the solution from http://mahmoudoracle.blogspot.in/2012/07/create-insert-statement-for-table-data.html
Just for quick copy paste, I copied the proc from there and keeping it here.
Let's Now run function for table EMP
The output is select statement that we should run it to get insert statement of data.
If we run the previous select statement it will generate inert statement for data existed in table EMP.
Made few changes to above GEN_INSERT_STATEMENT to give insert script for table which have too many columns..
I googled for the solution, I just got the solution from http://mahmoudoracle.blogspot.in/2012/07/create-insert-statement-for-table-data.html
Just for quick copy paste, I copied the proc from there and keeping it here.
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);
Made few changes to above GEN_INSERT_STATEMENT to give insert script for table which have too many columns..
CREATE OR REPLACE FUNCTION GEN_INSERT_STATEMENT (IN_TABLE_NAME VARCHAR2)
RETURN CLOB
IS
LC$COLS_SELECT CLOB;
LC$COLS_VALUES CLOB;
LC$COLOUMN CLOB;
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;
DECLARE
IN_TABLE_NAME VARCHAR2(100) := 'TABLE_NAME_HERE';
LC$COLS_SELECT CLOB;
LC$COLS_VALUES CLOB;
LC$COLOUMN CLOB;
CURSOR LCUR$TAB_COLUMNS (IN_TABLE_NAME VARCHAR2)
IS
SELECT COLUMN_NAME, DATA_TYPE, COLUMN_ID
FROM ALL_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;
dbms_output.put_line('SELECT ''INSERT INTO '
|| IN_TABLE_NAME
|| ' ('
|| LC$COLS_SELECT
|| ') VALUES ('
|| LC$COLS_VALUES
|| ');'' FROM '
|| IN_TABLE_NAME
|| ';');
END;
/
